Oracle Nested Table - Create, Insert and Select

Hi all

Today i want to speak about oracle nested table.
Basically you can define in oracle a field that is a table inserted in other table. In Oracle system tables there are two tables used to manage this particular type of columns :

• USER_NESTED_TABLES              : Nested Table List
• USER_NESTED_TABLE_COLS    : Nested Table List Columns

Now an example to test it.

1) Creation a Type :

CREATE OR REPLACE TYPE VALUELIST AS TABLE OF VARCHAR2(20);

2) Creation Table :

CREATE TABLE NESTED_TABLE
(  ID            NUMBER           NOT NULL,
   FIELD1   VALUELIST)
NESTED TABLE FIELD1 STORE AS field1_tab;

3) Inserting values in table :

INSERT INTO nested_table VALUES (1, valuelist('Red'));

INSERT INTO nested_table VALUES (2, valuelist('White', 'Brown'));

INSERT INTO nested_table VALUES (3, valuelist('Blue', 'Orange','Yellow'));

4) Select values :

SELECT id, COLUMN_VALUE FROM nested_table t1, TABLE(t1.field1) t2;

5) Result :

ID    COLUMN_VALUE

1 Red

2 White

2 Brown

3 Blue

3 Orange

3 Yellow

In select statement you must reference a pseudo-column called "COLUMN_VALUE". To select values from a nested table, always you must explain mother table too.

Stay tuned.