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 := '[email protected]';
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
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.