96. VARRAYS AND NESTED TABLES

VARRAYS

A varying array allows you to store repeating attributes of a record in a single row but with limit.

Ex:

    1) We can create varrays using oracle types as well as user defined types.

         a) Varray using pre-defined types

              SQL> Create type va as varray(5) of varchar(10);/

         b) Varrays using user defined types

              SQL> Create type addr as object(hno number(3),city varchar(10));/

              SQL> Create type va as varray(5) of addr;/

    2) Using varray in table

         SQL> Create table student(no number(2),name varchar(10),address va);

    3) Inserting values into varray table

         SQL> Insert into student values(1,’sudha’,va(addr(111,’hyd’)));

         SQL> Insert into student values(2,’jagan’,va(addr(111,’hyd’),addr(222,’bang’)));

    4) Selecting data from varray table

         SQL> Select * from student;

         -- This will display varray column data along with varray and adt;

         SQL> Select no,name, s.* from student s1, table(s1.address) s;

         -- This will display in general format

    5) Instead of s.* you can specify the columns in varray

         SQL> Select no,name, s.hno,s.city from student s1,table(s1.address) s;

    -- Update and delete not possible in varrays.

    -- Here we used table function which will take the varray column as input for producing

        output excluding varray and types.

       NESTED TABLES

A nested table is, as its name implies, a table within a table. In this case it is a table that is represented as a column within another table.

Nested table has the same effect of varrays but has no limit.

Ex:

    1) We can create nested tables using oracle types and user defined types which has no

         limit.

         a) Nested tables using pre-defined types

              SQL> Create type nt as table of varchar(10);/

         b) Nested tables using user defined types

              SQL> Create type addr as object(hno number(3),city varchar(10));/

              SQL> Create type nt as table of addr;/

    2) Using nested table in table

         SQL> Create table student(no number(2),name varchar(10),address nt) nested table 

                  address store as student_temp;

    3) Inserting values into table which has nested table

         SQL> Insert into student values (1,’sudha’,nt(addr(111,’hyd’)));

         SQL> Insert into student values (2,’jagan’,nt(addr(111,’hyd’),addr(222,’bang’)));

    4) Selecting data from table which has nested table

         SQL> Select * from student;

         -- This will display nested table column data along with nested table and adt;

         SQL> Select no,name, s.* from student s1, table(s1.address) s;

         -- This will display in general format

    5) Instead of s.* you can specify the columns in nested table

         SQL> Select no,name, s.hno,s.city from student s1,table(s1.address) s;

    6) Inserting nested table data to the existing row

         SQL> Insert into table(select address from student where no=1)

                  values(addr(555,’chennai’));

    7) Update in nested tables

         SQL> Update table(select address from student where no=2) s set s.city=’bombay’

                 where s.hno = 222;

    8) Delete in nested table

         SQL> Delete table(select address from student where no=3) s where s.hno=333;

DATA MODEL

Ø  ALL_COLL_TYPES          

Ø  ALL_TYPES               

Ø  DBA_COLL_TYPES          

Ø  DBA_TYPES               

Ø  USER_COLL_TYPES         

Ø  USER_TYPES