Mega Code Archive
Alter table to add varray type column
<![CDATA[
SQL>
SQL> create table courses
2 ( code VARCHAR2(6) constraint C_PK primary key
3 , description VARCHAR2(30)
4 , category CHAR(3)
5 , duration NUMBER(2)
6 ) ;
Table created.
SQL> insert into courses values('SQL','SQL','GEN',4);
1 row created.
SQL> insert into courses values('OAU','Java','GEN',1);
1 row created.
SQL> insert into courses values('JAV','C++','BLD',4);
1 row created.
SQL> insert into courses values('PLS','C','BLD',1);
1 row created.
SQL> insert into courses values('XML','XML','BLD',2);
1 row created.
SQL> insert into courses values('ERM','ERP','DSG',3);
1 row created.
SQL> insert into courses values('PMT','ERP','DSG',1);
1 row created.
SQL> insert into courses values('RSD','jQuery','DSG',2);
1 row created.
SQL> insert into courses values('PRO','Linux','DSG',5);
1 row created.
SQL> insert into courses values('GEN','Oracle','DSG',4);
1 row created.
SQL>
SQL>
SQL>
SQL> create table c
2 as
3 select * from courses;
Table created.
SQL>
SQL> create or replace type erratum_t 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 or replace type errata_tab_t as table of erratum_t;
2 /
Type created.
SQL>
SQL> describe errata_tab_t
errata_tab_t TABLE OF ERRATUM_T
Name Null? Type
----------------------------------------- -------- ----------------------------
CODE VARCHAR2(4)
CH NUMBER(2)
PG NUMBER(3)
TXT VARCHAR2(40)
SQL>
SQL> alter table c
2 add (errata errata_tab_t)
3 nested table errata store as errata_tab;
Table altered.
SQL>
SQL> update c
2 set errata = errata_tab_t();
10 rows updated.
SQL>
SQL> drop table c;
Table dropped.
SQL> drop type errata_tab_t force;
Type dropped.
SQL> drop type erratum_t force;
Type dropped.
SQL>
SQL>
SQL> drop table courses;
Table dropped.
]]>