Referencing

While foreign keys express many-to-one relationships in the relational model a more efficient way of expressing many-to-one relationships is provided by using Oracle’s object model. As was earlier described, Oracle gives every row object a unique identifier called the OID. The OID allows other objects to reference existing row objects. To hold this reference Oracle uses a built in data type called REF (Oracle, 1997b, p. 6). Here we can see how the REF functions works:
select REF(P)
from NEWPERSON P
where LastName = ‘DOE’;

REF(P)
-----------------------------------------------------------------------
00002802095C3AD6AD3B3C4CD7B080A49C97BACD9835619CD6E25C49F8AC8879791B33DA8F0040F0590002

The REF function takes as an input the alias given to object table. In the example above it is “P”. Here we see the REF function return the actual OID for the row object selected. Using REF by itself isn’t very useful, but Oracle provides another function called DREF that translates REF output into the actual values of the row objects (Koch, 2002, p. 612).
The previously given example of the NEWPERSON table will be used for the next example where a new table is created to hold the department that people belong to.
create table NEWDEPARTMENT
(DeptName VARCHAR(30),
PersonIn REF NEWPERSON_TY);

The first part of the create table syntax looks normal, but the last line is not part of the relational model:
PersonIn REF NEWPERSON_TY);

The PersonIn column does not hold any meaningful data itself, but references data that is stored somewhere else. This reference location can be anywhere as long as the row object referenced is a NEWPERSON_TY type object (Koch, 2002, p. 613). To get a full description of the table just created:
Set describe depth 2
Desc NEWDEPARTMENT

Name Null? Type
--------------- -------- -------------------
DEPTNAME VARCHAR2(30)
PERSONIN REF OF NEWPERSON_TY
FIRSTNAME VARCHAR2(25)
LASTNAME VARCHAR2(25)
BIRTHDATE DATE

METHOD
------
MEMBER FUNCTION AGE RETURNS NUMBER
Argument Name Type In/Out Default?
------------------------------ -------- ------ --------
BIRTHDATE DATE IN

To insert a record into NEWDEPARTMENT, the REF function is needed to store the NEWPERSON reference in the PersonIn column:

insert into NEWDEPARTMENT
select 'Research',
REF(P)
from NEWPERSON P
where LastName = 'DOE';

The first two lines are fairly straightforward. The literal value “Research” is inserted into the NEWPERSON table. Since the datatype of the PersonIn column is of NEWPERSON_TY, a reference to the NEWPERSON object table can be used. What takes place is the REF function returns the OID from the query on the selected NEWPERSON object. Then in the NEWDEPARTMENT table the OID is stored as a pointer to the row object in the NEWPERSON object table. The NEWDEPARTMENT table never keeps the NEWPERSON information just the name of the department (DEPTNAME) and an OID pointer (Koch, 2002, p. 614). Evidence of this can be seen by doing the following:
Select * from NEWDEPARTMENT

DEPTNAME
------------------------------
PERSONIN
-----------------------------------------------------------------------
Research
00002202085C3AD6AD3B3C4CD7B080A49C97BACD9835619CD6E25C49F8AC8879791B33DA8F

The referenced value cannot be seen unless the DREF function is used. The DREF function takes the OID and evaluates the reference to return a value.
select DEREF(D.PersonIn)
from NEWDEPARTMENT D
where DEPTNAME = 'Research'

DEREF(D.PERSONIN)(FIRSTNAME, LASTNAME, BIRTHDATE)
----------------------------------------------------
NEWPERSON_TY('JOHN', 'DOE', '03-FEB-70')

This shows that the NEWPERSON record JOHN DOE is referenced by the Research record in NEWDEPARTMENT.
There are important differences in these object queries when compared to relational queries. The first significant difference is that without specifying the join criteria, joins are performed in the background (Koch, 2002, p. 614). This was shown when a query used a reference to a row object that traveled from the table NEWDEPARTMENT to the table PERSON. The second important factor is that you can DEREF values without the need to know the name of the object table (Koch, 2002, p. 615). In the example above the only table mentioned in the query is NEWDEPARTMENT. This turns the relational model on its head. An embedded foreign key would never be needed in the NEWPERSON table to describe which department a person is in (Burleson, 2002, p. 12).
The function DEREF is used to get the reference of the object table from the relational table, but to gather the same structure of the object type of an object table the VALUE function is required (Koch, 2002).
select value(p)
from newperson p
where lastname = 'DOE'

VALUE(P)(FIRSTNAME, LASTNAME, BIRTHDATE)
-----------------------------------------
NEWPERSON_TY('JOHN', 'DOE', '03-FEB-70')

Lets take our Oracle object model to PL/SQL. Here is an example of how we can use the VALUE function to return specific attributes from our object newperson (Koch, 2002):
set serveroutput on
declare
v_person NEWPERSON_TY;
begin
select value(p) into v_person
from NEWPERSON p
where lastname = 'DOE';
DBMS_OUTPUT.PUT_LINE(v_person.firstname);
DBMS_OUTPUT.PUT_LINE(v_person.lastname);
DBMS_OUTPUT.PUT_LINE(v_person.birthdate);
end;


Giving the results:

JOHN
DOE
03-FEB-70

Next an example of an insert:
declare
v_new_newperson NEWPERSON_TY;
begin
v_new_newperson := NEWPERSON_TY('JANE', 'SMITH', '04-JAN-75');
insert into NEWPERSON
values (v_new_newperson);
end;