Mega Code Archive
Insert value into table with nested type colunm
<![CDATA[
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> 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> 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> 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>
]]>