Lesson 2	Understanding related object tables (the REF operator)
Objective	Describe the SQL enhancements that allow you to query related object tables.

Understanding related Object Tables in Oracle

Within Oracle, references can be obtained for row objects only. For every row object that is stored within an object table, Oracle generates and associates an object identifier (OID) for it. However, objects that are stored within a column do not have such a unique identifier. In the following series of images, we create an object and reference it within another object by using the REF pointer:

1) Using the Reference Operator — 1) First, we create an object CUSTOMER_TYPE, which has information about a customer, including the customer's name, address, and phone numbers.

2) Now that we have the CUSTOMER_TYPE object, we will reference it with SALE_HEADER table by using the REF pointer.

Using REF Operator

To query a table that is referenced to an object, we can use standard SQL along with the VALUE, REF, and DEREF operators. Let's examine these operators in detail.

REF operator

Object Unique Identifier will return a REF (reference) of the object, rather than the object itself. Like the VALUE operator, REF takes a correlation variable^[1] as an argument. In this context, a correlation variable is simply a table alias.

More on The REF operator

Typically, a REF value comprises the object's unique identifier (OID) associated with the object table and the row identifier (ROWID) of the row object. The ROWID is used as a means to provide quick access to the object. A REF can be used for implicit joins and easy navigation. REF may sound a lot like a foreign key. However, there are important differences. REF is more reliable and persistent than a foreign key. This is because REFs do not refer to user-changeable values. Rather, they refer to invisible, internal values such as OID and ROWID.

Objects implemented through TYPEs

Oracle implements Objects through the use of TYPEs, defined in a similar way to packages.
Unlike packages in which the instance of the package is limited to the current session, an instance of an object type can be stored in the database for later use. The definition of the type contains a comma separated list of attributes and properties, defined in the same way as

package variables, and
member functions or procedures.

If a type contains member functions or procedures, the procedural work for these elements is defined in the TYPE BODY.
To see how objects can be used, suppose an object is created to represent a person. In this case, a person is defined by three attributes: first_name, last_name and date_of_birth. Returning the age of the person is also desired, so this is included as a member function, get_age.

CREATE OR REPLACE TYPE t_person AS OBJECT (
   first_name     VARCHAR2(30),
   last_name      VARCHAR2(30),
   date_of_birth  DATE,
   MEMBER FUNCTION get_age RETURN NUMBER
 );
 /

Type created.

The following example illustrates this:

SELECT REF(pc)
FROM pet_care_log_obj_table pc
WHERE to_char(pc.last_update_datetime, 'DD-MMM-YY') = 
 '12-DEC-99';

Executing this SQL returns the following result set:

REF(PC)
------------------------------------------------------
0000280209DEE8F40AC4B911D39B07000000000000DEE8F3E3C4B9
 11D39B07000000000000008003370002

DEREF Operator

Given a reference, DEREF will return the original object. We will learn more about DEREF later in this module. The following example illustrates this:
The link below contains additional information about Database Design.

SELECT DEREF(pc.product_id)
FROM pet_care_log_obj_table pc
WHERE to_char(pc.last_update_datetime, 'DD-MMM-YY') =
 '12-DEC-99';

Executing this SQL returns the following result set:

DEREF(PC.PRODUCT_ID)(PRODUCT_ID, PRODUCT_NAME, 
 PACKAGE_ID, CURRENT_INVENTORY_COUNT, STORE_COST, 
 SALE
---------------------------------------------------
PRODUCT_TYPE(11, 'Fish', NULL, 150, 0, .99, 
 '23-OCT-99', 'JANET', 'Y')

VALUE operator

The VALUE operator returns an object, rather than a list of attributes. It takes a correlation variable as an argument. In this context, a correlation variable is simply a table alias. The following example illustrates how the VALUE operator is used:

SELECT VALUE(pc)
FROM pet_care_log_obj_table pc
WHERE to_char(pc.last_update_datetime, 'DD-MMM-YY') = '12-DEC-99';

The result set of this query is a set of objects, not a set of attributes, as shown below:

VALUE(PC)(PRODUCT_ID, LOG_DATETIME, CREATED_BY_USER,
 LOG_TEXT, LAST_UPDATE_DATETIME)
----------------------------------------------------
PET_CARE_LOG_TYPE(0000220208DEE8F3F3C4B911D39B07000
 000000000DEE8F3E2C4B911D39B07000000000000, '12-DE
C-99', 'MARK', 'Fish that are sold must be written 
 down on the FISH LOG sheet for proper funds if 
 needed.', '12-DEC-99')

[1] Correlation variable: A correlation variable is a parameter, which is passed when a function is used, e.g. the VALUE finction requires the table alias to be passed as a parameter or correlation variable.

Object Oriented Databases