Ref and Deref Constructs:

The OID holds no actual data but merely "points" to the object table. An object identifier (OID) permits the corresponding object to be referred to from other objects or from relational tables. The built-in datatype called REF represents like references. A REF is a container for an object identifier. The REFs are pointers to objects. Only operation Oracle permits on a REF is to replace its contents with a reference to a variant object. A REF encapsulates a reference to a row object of a specified object type.

A table can have top-level REF columns or it can have REF attributes embedded within an object column. A Tables can have a combination of REF columns and REF attributes, every referencing a different object table.

Example

Let consider the deptab object table created using the dep_type abstract datatype :

CREATE TABLE EMPLOYEES (

EMPNO NUMBER,

ename VARCHAR2(20), deptno REF dep_type );

In the above instance, DEPTNO column references data which is stored elsewhere. It points the DEPTNO column to the DEP_TYPE datatype.

INSERT INTO EMPLOYEES

SELECT 2,'Kalyani',REF(X) FROM DEPTAB X

WHERE depno=10;

The deptno column holds the reference to the row object, not the value of the data stored in the row object.

The REF operator takes a row object as its argument and returns a reference value. The DEREF operator will performs the opposite function - it takes a Reference value and returns the value of the row objects.  It takes as its argument the OID produced for a reference.

In the given example, the reference OID can be seen through quering EMPLOYEES table.

SELECT         *          FROM EMPLOYEES;

Displays,

EMPNO          ENAME          DEPTNO

-----------          --------------------           --------------------

2 Kalyani                    

00002202087867EC0FCE3511D4BDBA008048DB6C207867EC0ECE3511D4BDBA008048DB6C2

0

The deptno column holds the reference to the row object it is not the value of the data stored in the row object. It will take the OID from deptno column and search the referenced object; the operator will evaluate the reference and return the values to the user.

To describe the row object the deptno column in the EMPLOYEES table refers use:

SELECT DEREF(K.deptno)  FROM employees K

WHERE ename = 'Kalyani' ;

DEREF(K.DEPTNO)(DEPNO, DNAME, LOC)

DEP_TYPE(10, 'sales', 'chennai')

To show selective information from the rowobject the deptno column in the EMPLOYEES table refers

SELECT  k.ENAME,k.DEPTNO.DNAME  FROM  EMPLOYEES K ;

This would shows,

ENAME          DEPTNO.DNAME

---------------     ------------------

Kalyani            sales