Using a Nested Table Type to Define a Column in a Table : Nested Table Column « Collections « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. Collections
3. Nested Table Column

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.