Alter table to add varray type column : Add Column « Table « Oracle PL / SQL






  1. Oracle PL / SQL
  2. Table
  3. Add Column

Alter table to add varray type column

  
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.








Related examples in the same category

1.Add a char type column to a table
2.Add a date type column to a table
3.Alter table: add a column to an existing table
4.Add two columns to a table
5.Alter a table to add two columns and use select to check
6.Alter table to add two columns and then drop them
7.Alter table to add a column and then add constraint to it
8.Alter table to add a column and then add 'check' constraint to it
9.Alter table to add a primary key
10.Alter table to add date type column
11.Add more columns
12.Add new column with default value and not null