Table with subquery : Nested Tables « Collections « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. Collections
3. Nested Tables

SQL>
SQL>
SQL> create table courses
  2  ( code        VARCHAR2(6)
  3  , description VARCHAR2(30)
  4  , category    CHAR(3)
  5  , duration    NUMBER(2)) ;

Table created.

SQL>
SQL>
SQL> insert into courses values('SQL','SQL course',    'GEN',4);

1 row created.

SQL> insert into courses values('OAU','Oracle course', 'GEN',1);

1 row created.

SQL> insert into courses values('JAV','Java course',   'BLD',4);

1 row created.

SQL> insert into courses values('PLS','PL/SQL course', 'BLD',1);

1 row created.

SQL> insert into courses values('XML','XML course',    'BLD',2);

1 row created.

SQL> insert into courses values('ERM','ERM course',    'DSG',3);

1 row created.

SQL> insert into courses values('PMT','UML course',    'DSG',1);

1 row created.

SQL> insert into courses values('RSD','C# course',     'DSG',2);

1 row created.

SQL> insert into courses values('PRO','C++ course',    'DSG',5);

1 row created.

SQL> insert into courses values('GEN','GWT course',    'DSG',4);

1 row created.

SQL>
SQL>
SQL> create type errorNumberType as object
  2  ( code varchar2(4)
  3  , ch   number(2)
  4  , pg   number(3)
  5  , txt  varchar2(40)
  6  ) ;
  7  /

Type created.

SQL>
SQL> create type errorNumberTableCollection as table of errorNumberType;
  2  /

Type created.

SQL>
SQL> create table c as select * from courses;

Table created.

SQL>
SQL> alter table c
  2  add (errata errorNumberTableCollection)
  3  nested table errata store as errata_tab;

Table altered.

SQL>
SQL> update c set errata = errorNumberTableCollection();

10 rows updated.

SQL>
SQL>
SQL> insert into table ( select errata from c where  code = 'SQL' )
  2  values ( 'SQL', 3, 45, 'line' );

1 row created.

SQL>
SQL>
SQL> set linesize 70
SQL> break on row page
SQL>
SQL>
SQL> update table ( select errata from c where  code = 'SQL') e
  2  set    e.ch  = 7;

1 row updated.

SQL>
SQL>
SQL> clear breaks
breaks cleared
SQL>
SQL>
SQL> drop table courses;

Table dropped.

SQL> drop table c;

Table dropped.

SQL> drop type errorNumberTableCollection force;

Type dropped.

SQL> drop type errorNumberType    force;

Type dropped.