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 SlideShow, we create an object and reference it within another object 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. 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.