Oracle Varrys and Nested Tables with examples

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,’Rama’,va(addr(111,’hyd’)));

         SQL> Insert into student values(2,’RESHWANTH’,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,’Rama’,nt(addr(111,’hyd’)));

         SQL> Insert into student values (2,’RESHWANTH’,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;

FLASHBACK QUERY

Used to retrieve the data which has been already committed with out going for recovery.

Flashbacks are of two types

Ø  Time base flashback

Ø  SCN based flashback (SCN stands for System Change Number)

Ex:

1) Using time based flashback

     a) SQL> Select *from student;

          -- This will display all the rows

     b) SQL> Delete student;

     c) SQL> Commit;              -- this will commit the work.

     d) SQL> Select *from student;

         -- Here it will display nothing

     e) Then execute the following procedures

         SQL> Exec dbms_flashback.enable_at_time(sysdate-2/1440)

     f) SQL> Select *from student;

         -- Here it will display the lost data

         -- The lost data will come but the current system time was used

     g) SQL> Exec dbms_flashback.disable

          -- Here we have to disable the flashback to enable it again

2) Using SCN based flashback

     a) Declare a variable to store SCN

          SQL> Variable s number

     b) Get the SCN

          SQL> Exec :s := exec dbms_flashback.get_system_change_number

     c) To see the SCN

         SQL> Print s

     d) Then execute the following procedures

          SQL> Exec dbms_flashback.enable_at_system_change_number(:s)

          SQL> Exec dbms_flashback.disable

EXTERNAL TABLES

You can user external table feature to access external files as if they are tables inside the database.

When you create an external table, you define its structure and location with in oracle.

When you query the table, oracle reads the external table and returns the results just as if the data had been stored with in the database.

ACCESSING EXTERNAL TABLE DATA

To access external files from within oracle, you must first use the create directory command to define a directory object pointing to the external file location

Users who will access the external files must have the read and write privilege on the directory.

Ex:

CREATING DIRECTORY AND OS LEVEL FILE

    SQL> Sqlplus system/manager

    SQL> Create directory Prasad_dir as ‘/Visdb/visdb/9.2.0/external’;

     SQL> Grant all on directory Prasad_dir to Prasad;

     SQL> Conn Prasad/Prasad

     SQL> Spool dept.lst

     SQL> Select deptno || ‘,’ || dname || ‘,’ || loc from dept;

     SQL> Spool off

CREATING EXTERNAL TABLE

 SQL> Create table dept_ext

         (deptno number(2),

         Dname varchar(14),

         Loc varchar(13))

         Organization external  ( type oracle_loader

                                                 Default directory Prasad_dir

                                                 Access parameters

                                                 ( records delimited by newline

                                                    Fields terminated by “,”

                                                    ( deptno number(2),

                                                      Dname varchar(14),

                                                      Loc varchar(13)))

         Location (‘/Visdb/visdb/9.2.0/dept.lst’));

SELECTING DATA FROM EXTERNAL TABLE

SQL> select * from dept_ext;

This will read from dept.lst which is a operating system level file.

LIMITATIONS ON EXTERNAL TABLES

a)   You can not perform insert, update, and delete operations

a)   Indexing not possible

b)   Constraints not possible

BENEFITS OF EXTERNAL TABLES

a)   Queries of external tables complete very quickly even though a full table scan id required with each access

b)   You can join external tables to each other or to standard tables

REF DEREF VALUE

REF

Ø  The ref function allows referencing of existing row objects.

Ø  Each of the row objects has an object id value assigned to it.

Ø  The object id assigned can be seen by using ref function.

DEREF

Ø  The deref function performs opposite action.

Ø  It takes a reference value of object id and returns the value of the row objects.

VALUE

Ø  Even though the primary table is object table, still it displays the rows in general format.

Ø  To display the entire structure of the object, this will be used.

Ex:

    1) create vendot_adt type

           SQL> Create type vendor_adt as object (vendor_code number(2), vendor_name

                 varchar(2),  vendor_address varchar(10));/

    2) create object tables vendors and vendors1

           SQL> Create table vendors of vendor_adt;

           SQL> Create table vendors1 of vendor_adt;

    3) insert the data into object tables

         SQL> insert into vendors values(1, ‘a’, ‘hyd’);

           SQL> insert into vendors values(2, ‘b’, ‘bang’);

           SQL> insert into vendors1 values(3, ‘c’, ‘delhi’);

           SQL> insert into vendors1 values(4, ‘d’, ‘chennai’);

    4) create another table orders which holds the vendor_adt type also.

         SQL> Create table orders (order_no number(2), vendor_info ref vendor_adt);

                                                     Or

         SQL> Create table orders (order_no number(2), vendor_info ref vendor_adt with rowid);

   

     5) insert the data into orders table

          The vendor_info column in the following syntaxes will store object id of any table which   

          is referenced by vendor_adt object ( both vendors and vendors1).

         

          SQL> insert into orders values(11,(select ref(v) from vendors v where vendor_code = 1));

          SQL> insert into orders values(12,(select ref(v) from vendors v where vendor_code = 2));

          SQL> insert into orders values(13,(select ref(v1) from vendors1 v1 where vendor_code =

                  1));

          SQL> insert into orders values(14,(select ref(v1) from vendors1 v1 where vendor_code =

                  1));

     6) To see the object ids of vendor table

            SQL> Select ref(V) from vendors v;

     7) If you see the vendor_info of orders it will show only the object ids not the values, to see

          the values

            SQL> Select deref(o.vendor_info) from orders o;

     8) Even though the vendors table is object table it will not show the adt along with data, to

          see the data along with the adt

            SQL>Select * from vendors;

          This will give the data without adt.

            SQL>Select value(v) from vendors v;

          This will give the columns data along wih the type.

REF CONSTRAINTS

Ref can also acts as constraint.

Even though vendors1 also holding vendor_adt, the orders table will store the object ids of vendors only because it is constrained to that table only.

The vendor_info column in the following syntaxes will store object ids of vendors only.

SQL> Create table orders (order_no number(2), vendor_info ref vendor_adt scope is

         vendors);

                                                     Or

SQL> Create table orders (order_no number(2), vendor_info ref vendor_adt constraint fk

         references vendors);

          OBJECT VIEWS WITH REFERENCES

To implement the objects and the ref constraints to the existing tables, what we can do? Simply drop the both tables and recreate with objects and ref constrains.

But you can achieve this with out dropping the tables and without losing the data by creating object views with references.

Ex:

     a) Create the following tables

          SQL> Create table student1(no number(2) primary key,name varchar(2),marks

                  number(3));

          SQL> Create table student2(no number(2) primary key,hno number(3),city varchar(10),id

                  number(2),foreign Key(id) references student1(no));

     b) Insert the records into both tables

          SQL> insert into student1(1,’a’,100);

          SQL> insert into student1(2,’b’,200);

          SQL> insert into student2(11,111,’hyd’,1);

          SQL> insert into student2(12,222,’bang’,2);

          SQL> insert into student2(13,333,’bombay’,1);

     c) Create the type

         SQL> create or replace type stud as object(no number(2),name varchar(2),marks

                 number(3));/

     d) Generating OIDs

          SQL> Create or replace view student1_ov of stud with object identifier(or id) (no) as

                  Select * from Student1;

     e) Generating references

          SQL> Create or replace view student2_ov as select no,hno,city,make_ref(student1_ov,id)

                   id from Student2;

     d) Query the following

          SQL> select *from student1_ov;

          SQL> select ref(s) from student1_ov s;

          SQL> select values(s) from student1_ov;

          SQ> select *from student2_ov;

          SQL> select deref(s.id) from student2_ov s;