0
Category:

NESTED TABLE

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

Examples

Create a table with NESTED TABLE column:

CREATE TYPE t_emp AS OBJECT (id NUMBER, name VARCHAR2(20))
/
CREATE TYPE t_emplist AS TABLE OF t_emp
/

CREATE TABLE dept_new (id NUMBER, emps t_emplist)
    NESTED TABLE emps STORE AS emp_table;

INSERT INTO dept_new VALUES (
    10, 
    t_emplist(
        t_emp(1, 'SCOTT'),
        t_emp(2, 'BRUCE')));


SELECT d.id, e.id N_ID,e.name N_NAME
        FROM dept_new d, TABLE(d.emps) e   ;