Mega Code Archive

 

Categories / Oracle PLSQL Tutorial / Collections

 

Using a Nested Table Type to Define a Column in a Table

Once you have defined your nested table type, you can use it to define a column in a table. The NESTED TABLE clause identifies the name of the nested table column (addresses). The STORE AS clause specifies the name of the actual nested table (nested_addresses). You cannot access the nested table independently of the table in which it is embedded. SQL> SQL> CREATE Or Replace TYPE AddressType AS OBJECT (   2    street VARCHAR2(15),   3    city   VARCHAR2(15),   4    state  CHAR(2),   5    zip    VARCHAR2(5)   6  );   7  / SQL> SQL> CREATE Or Replace TYPE nested_table_AddressType AS TABLE OF AddressType;   2  / Type created. SQL> SQL> CREATE TABLE employee (   2    id         INTEGER PRIMARY KEY,   3    first_name VARCHAR2(10),   4    last_name  VARCHAR2(10),   5    addresses  nested_table_AddressType   6  )   7  NESTED TABLE   8    addresses   9  STORE AS  10    nested_addresses; Table created. SQL> SQL> desc employee;  Name           Null?    Type  ID             NOT NULL NUMBER(38)  FIRST_NAME              VARCHAR2(10)  LAST_NAME               VARCHAR2(10)  ADDRESSES               NESTED_TABLE_ADDRESSTYPE    STREET                VARCHAR2(15)    CITY                  VARCHAR2(15)    STATE                 CHAR(2)    ZIP                   VARCHAR2(5) SQL> SQL> drop table employee; Table dropped.