99. 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);