Insert value into table with nested type colunm : 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
  2  set    errata = errorNumberTableCollection();

10 rows updated.

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

1 row created.

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.

SQL>