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 typeSQL> Create type vendor_adt as object (vendor_code number(2), vendor_name varchar(2), vendor_address varchar(10));/
2) create object tables vendors and vendors1SQL> Create table vendors of vendor_adt; SQL> Create table vendors1 of vendor_adt;
3) insert the data into object tablesSQL> 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 tableSQL> 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 valuesSQL> 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 adtSQL>Select * from vendors;
This will give the data without adt.SQL>Select value(v) from vendors v;
This will give the columns data along with 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.