Using Nested Table variables / Collections in SQL inside PL/SQL blocks

Question

I first create an address_type object

CREATE TYPE address_type AS OBJECT
( line1                         VARCHAR2(100)
, line2                         VARCHAR2(100)
, line3                         VARCHAR2(100)
, city                          VARCHAR2(50)
, state                         VARCHAR2(50)
, country                       VARCHAR2(50)
, zip                           VARCHAR2(10)
);
/

I create a nested table type of the above object.

CREATE TYPE address_table AS TABLE OF ADDRESS_TYPE;
/

I then create another object as follows:

CREATE TYPE telephone_number_type AS OBJECT
( country_code                  VARCHAR2(4)
, area_code                     VARCHAR2(10)
, phone_number                  VARCHAR2(10)
, extension                     VARCHAR2(10)
, number_type                   VARCHAR2(10)
);
/

And then I create a nested table type as follows:

CREATE TYPE telephone_number_table AS TABLE OF TELEPHONE_NUMBER_TYPE;
/

Now I create a table named person. Many of whose columns are not much useful in this question, except for the telephone_numbers column which is of nested table telephone_number_table type.

CREATE TABLE person
( personid                       INTEGER          PRIMARY KEY
, fname                          VARCHAR2(50)     NOT NULL
, mname                          VARCHAR2(50)
, lname                          VARCHAR2(50)     NOT NULL
, email                          VARCHAR2(255)    UNIQUE
, password                       VARCHAR2(255)    NOT NULL
, birthdate                      DATE
, billing_address                ADDRESS_TABLE
, delivery_address               ADDRESS_TABLE
, telephone_numbers              TELEPHONE_NUMBER_TABLE
, display_pic                    BLOB
, ts_registration                TIMESTAMP
, ts_verification                TIMESTAMP
, ts_last_updated                TIMESTAMP
) NESTED TABLE billing_address STORE AS nt_billing_address
, NESTED TABLE delivery_address STORE AS nt_delivery_address
, NESTED TABLE telephone_numbers STORE AS nt_telephone_numbers
, LOB(display_pic) STORE AS SECUREFILE (
  TABLESPACE users
  ENABLE STORAGE IN ROW
  CHUNK 4096
  PCTVERSION 20
  NOCACHE
  NOLOGGING
  COMPRESS HIGH
)
;

I then create a sequence for this:
```
CREATE SEQUENCE sq_personid;
```

To insert values into the person table I use an anonymous block as follows:

DECLARE
  v_fname person.fname%TYPE := 'Yogeshwar';
  v_mname person.mname%TYPE := '';
  v_lname person.lname%TYPE := 'Rachcha';
  v_email person.email%TYPE := 'yogeshrachcha@gmail.com';
  v_password person.password%TYPE := 'mail_123';
  v_birthdate person.birthdate%TYPE := TO_DATE('28-03-1987', 'DD-MM-YYYY');
  v_telephone_numbers TELEPHONE_NUMBER_TABLE;
  v_billing_address   ADDRESS_TABLE;
  v_delivery_address  ADDRESS_TABLE;
BEGIN
  v_telephone_numbers := TELEPHONE_NUMBER_TABLE
                           ( TELEPHONE_NUMBER_TYPE('+91','22','123456','','Residence')
                                                   , TELEPHONE_NUMBER_TYPE('+91','22','456798','123','Office')
                                                   , TELEPHONE_NUMBER_TYPE('+91','','1234567890','','Mobile'));

  v_billing_address := ADDRESS_TABLE (ADDRESS_TYPE ( 'Line 1', 'Line 2', 'Line 3', 'Mumbai', 'Maharashtra', 'India', '123456'));
  v_delivery_address := ADDRESS_TABLE (ADDRESS_TYPE ( 'Line 1', 'Line 2', 'Line 3', 'Mumbai', 'Maharashtra', 'India', '123456'));
  -- billing and delivery addresses are the same. These are not much importance in this question.

  INSERT INTO person VALUES
  ( sq_personid.nextval
  , v_fname
  , v_mname
  , v_lname
  , v_email
  , v_password
  , v_birthdate
  , v_billing_address
  , v_delivery_address
  , v_telephone_numbers
  , NULL
  , sysdate
  , sysdate
  , sysdate);

END;

Everything runs absolutely perfect till this point. Then in an anonymous block like the following, I try to create a nested table variable and use it in an SQL query:

DECLARE
  TYPE t_country_codes IS TABLE OF VARCHAR2(4);
  country_codes T_COUNTRY_CODES := T_COUNTRY_CODES('+1', '+44', '+91');
  cc VARCHAR2(4);
BEGIN
  FOR i IN (SELECT t.country_code
                          FROM person p
                          CROSS JOIN TABLE(p.telephone_numbers) t
                         WHERE t.country_code IN (SELECT COLUMN_VALUE -- I doubt the problem is with this SELECT statement.
                                                    FROM TABLE(country_codes))) LOOP
        dbms_output.put_line(i.country_code);
  END LOOP;
END;
/

I get this error:

ORA-06550: line 8, column 70:
PLS-00642: local collection types not allowed in SQL statements
ORA-06550: line 8, column 64:
PL/SQL: ORA-22905: cannot access rows from a non-nested table item

score 6 · Accepted Answer · answered Mar 20 '13 at 06:18

6

A nested table type can be declared either in SQL (via the CREATE TYPE statement like you did with the telephone_number_table type) or in PL/SQL (via the TYPE declaration on the DECLARE block). If you declare the type in PL/SQL, you cannot use an instance of the type in SQL. You would need to declare the type in SQL in order to use an instance of the type in SQL.

answered Mar 20 '13 at 06:18

Justin Cave

227,342
24
367
384

1

Oh, you mean I should declare the `t_country_codes` type outside the anonymous block using the `CREATE TYPE` statement? – Rachcha Mar 20 '13 at 06:20
@Rachcha - Correct. The `t_country_codes` collection would need to be declared in SQL, not in the PL/SQL block, in order to use it in a SQL statement. – Justin Cave Mar 20 '13 at 06:21

score 2 · Answer 2 · edited Mar 20 '13 at 06:23

2

Please, try to move your "local" (from last block) collection definition to the schema level.

Also, this post can be helpful.

edited Mar 20 '13 at 06:23

Rachcha

8,486
8
48
70

answered Mar 20 '13 at 06:16

evgenyl

7,837
2
27
32

Cannot create a real table for string the values in the nested table. Burleson's answer is not applicable in my case. Cannot accept. – Rachcha Mar 20 '13 at 06:27

Using Nested Table variables / Collections in SQL inside PL/SQL blocks

2 Answers2

Linked