REF DREF VALUE in oracle

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