Using REFs

Libraries classify their holdings within a strictly controlled set of subjects. For example, the Library of Congress might classify the book you're reading now in the following three subjects:

· Oracle (Computer file)

· PL/SQL (Computer program language)

· Relational databases

The Library of Congress uses a hierarchical subject tree: "Computer file" is the broader subject or parent of "Oracle," and "Computer program language" is the broader subject for "PL/SQL."

When classifying things, any number of subjects may apply to a particular catalog item in a many-to-many (M:M) relationship between subjects and holdings. In my simple library catalog, I will make one long list (table) of all available subjects. While a relational approach to the problem would then establish an "intersection entity" to resolve the M:M relationship, I have other options in object-relational land.

I will start with an object type for each subject:

CREATE TYPE subject_t AS OBJECT (name VARCHAR2(2000), broader_term_ref REF subject_t);

Each subject has a name and a broader term. However, I'm not going to store the term itself as a second attribute, but instead a reference to it. The third line of this type definition shows that I've typed the broader_term_ref attribute as a REF to a same-typed object. It's kind of like Oracle's old EMP table, with a MGR column whose value identifies the manager's record in the same table.

I now create a table of subjects:

CREATE TABLE subjects OF subject_t (CONSTRAINT subject_pk PRIMARY KEY (name), CONSTRAINT subject_self_ref FOREIGN KEY (broader_term_ref) REFERENCES subjects);

The foreign key begs a bit of explanation. Even though it references a table with a relational primary key, because the foreign key datatype is a REF, Oracle knows to use the table's object identifier instead. This support for the REF-based foreign key constraint is a good example of Oracle's bridge between the object and relational worlds.

Here are a few unsurprising inserts into this table (just using the default constructor):

INSERT INTO subjects VALUES (subject_t('Computer file', NULL));INSERT INTO subjects VALUES (subject_t('Computer program language', NULL));INSERT INTO subjects VALUES (subject_t('Relational databases', NULL));INSERT INTO subjects VALUES (subject_t('Oracle', (SELECT REF(s) FROM subjects s WHERE name = 'Computer file')));INSERT INTO subjects VALUES (subject_t('PL/SQL', (SELECT REF(s) FROM subjects s WHERE name = 'Computer program language')));

For what it's worth, you can list the contents of the subjects table, as shown here:

SQL> SELECT VALUE(s) FROM subjects s; VALUE(S)(NAME, BROADER_TERM_REF)---------------------------------------------------------------------------------SUBJECT_T('Computer file', NULL)SUBJECT_T('Computer program language', NULL)SUBJECT_T('Oracle', 00002202089FC431FBE6FB0599E0340003BA0F1F139FC431FBE6690599E0340003BA0F1F13) SUBJECT_T('PL/SQL', 00002202089FC431FBE6FC0599E0340003BA0F1F139FC431FBE6690599E0340003BA0F1F13) SUBJECT_T('Relational databases', NULL)

Even if that's interesting, it's not terribly useful. However, what's both interesting and useful is that I can easily have Oracle automatically "resolve" or follow those pointers. For example, I can use the DEREF function to navigate those ugly REFs back to their target row in the table:

SELECT s.name, DEREF(s.broader_term_ref).name bt FROM subjects s;

Dereferencing is like an automatic join, although it's more of an outer join than an equi-join. In other words, if the reference is null or invalid, the driving row will still appear, but the target object (and column) will be null.

Oracle introduced a dereferencing shortcut that is really quite elegant. You only need to use dot notation to indicate what attribute you wish to retrieve from the target object:

SELECT s.name, s.broader_term_ref.name bt FROM subjects s;

Both queries produce the following output:

NAME BT------------------------------ ------------------------------Computer fileComputer program languageOracle Computer filePL/SQL Computer program languageRelational databases

As a point of syntax, notice that both forms require a table alias, as in the following:

SELECTtable_alias .ref_column_name FROM tablename table_alias

You can also use REF-based navigation in the WHERE clause. To show all the subjects whose broader term is "Computer program language," specify:

SELECT VALUE(s).name FROM subjects s WHERE s.broader_term_ref.name = 'Computer program language';

Although my example table uses a reference to itself, in reality a reference can point to an object in any object table in the same database. To see this in action, let's return to the definition of the base type catalog_item_t. I can now add an attribute that will hold a collection of REFs, so that each cataloged item can be associated with any number of subjects. First, I'll create a collection of subject references:

CREATE TYPE subject_refs_t AS TABLE OF REF subject_t;

And now I'll allow every item in the catalog to be associated with any number of subjects:

ALTER TYPE catalog_item_t ADD ATTRIBUTE subject_refs subject_refs_t CASCADE INCLUDING TABLE DATA;

Now (skipping gleefully over the boring parts about modifying any affected methods in the dependent types), I might insert a catalog record using the following exotic SQL statement:

INSERT INTO catalog_itemsVALUES (NEW book_t(10007, 'Oracle PL/SQL Programming', 'Sept 1997', CAST(MULTISET(SELECT REF(s) FROM subjects s WHERE name IN ('Oracle', 'PL/SQL', 'Relational databases')) AS subject_refs_t), '1-56592-335-9', 987));

The CAST/MULTISET clause performs an on-the-fly conversion of the subject REFs into a collection, as explained in Section 11.7 of Chapter 11.

Here is a slightly more understandable PL/SQL equivalent:

DECLARE subrefs subject_refs_t;BEGIN SELECT REF(s) BULK COLLECT INTO subrefs FROM subjects s WHERE name IN ('Oracle', 'PL/SQL', 'Relational databases'); INSERT INTO catalog_items VALUES (NEW book_t(10007, 'Oracle PL/SQL Programming', 'Sept 1997', subrefs, '1-56592-335-9', 987));END;

In English, that code says "grab the REFs to three particular subjects, and store them with this particular book."

REF-based navigation is so cool that I'll show another example using some more of that long-haired SQL:

SELECT VALUE(s).name || ' (' || VALUE(s).broader_term_ref.name || ')' plsql_subjects FROM TABLE(SELECT subject_refs FROM catalog_items WHERE id=10007) s;

This example retrieves values from the subjects table, including the name of each broader subject term, without ever mentioning the subjects table by name. (The TABLE function converts a collection into a virtual table.) Here are the results:

PLSQL_SUBJECTS------------------------------------Relational databases ()PL/SQL (Computer program language)Oracle (Computer file)

Other than automatic navigation from SQL, what else does all this effort offer the PL/SQL programmer? Er, well, not a whole lot. References have a slight edge, at least as theory goes, in that they are strongly typed—that is, a REF-typed column can point only to an object that is defined on the same object type as the REF. Contrast this behavior with foreign keys, which can point to any old thing as long as the target is constrained to be a primary key or has a unique index on it.