NESTED TABLE

NESTED TABLE is an Oracle data type used to support columns containing multivalued attributes, in this case, columns that can hold an entire sub-table.

Examples

Create a table with NESTED TABLE column:

CREATE OR REPLACE TYPE my_tab_t AS TABLE OF VARCHAR2(30);
/
CREATE TABLE nested_table (id NUMBER, col1 my_tab_t)
       NESTED TABLE col1 STORE AS col1_tab;

Insert data into table:

INSERT INTO nested_table VALUES (1, my_tab_t('A'));
INSERT INTO nested_table VALUES (2, my_tab_t('B', 'C'));
INSERT INTO nested_table VALUES (3, my_tab_t('D', 'E', 'F'));
COMMIT;

Select from nested table:

SQL> SELECT * FROM nested_table;
        ID COL1
---------- ------------------------
         1 MY_TAB_T('A')
         2 MY_TAB_T('B', 'C')
         3 MY_TAB_T('D', 'E', 'F')

Unnesting the subtable:

SQL> SELECT id, COLUMN_VALUE FROM nested_table t1, TABLE(t1.col1) t2;
        ID COLUMN_VALUE
---------- ------------------------
         1 A
         2 B
         2 C
         3 D
         3 E
         3 F
6 rows selected.

A more complicated multi-column nested table where customers can have multiple addresses:

CREATE TYPE address_t AS OBJECT (
   street  VARCHAR2(30),
   city    VARCHAR2(20),
   state   CHAR(2),
   zip     CHAR(5) );
/
CREATE TYPE address_tab IS TABLE OF address_t;
/
CREATE TABLE customers (
   custid  NUMBER,
   address address_tab )
NESTED TABLE address STORE AS customer_addresses;

INSERT INTO customers VALUES (1,
            address_tab(
              address_t('101 First', 'Redwood Shores', 'CA', '94065'),
              address_t('123 Maple', 'Mill Valley',    'CA', '90952')
            )                );

Unnesting the subtable:

SQL> select c.custid, u.*
  2  from customers c, table (c.address) u
  3  ;

    CUSTID STREET                         CITY                 ST ZIP
---------- ------------------------------ -------------------- -- -----
         1 101 First                      Redwood Shores       CA 94065
         1 123 Maple                      Mill Valley          CA 90952

Monitor

To see what nested tables were created, query the USER_NESTED_TABLES and USER_NESTED_TABLE_COLS views.

Also see

• VARRAY

Glossary of Terms
A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	#