The UTL_REF package

The UTL_REF built-in package performs the dereferencing operation without an explicit SQL call, allowing your application to perform a programmatic lock, select, update, or delete of an object given only its REF. As a short example, I can add a method such as the following to the subject_t type:

MEMBER FUNCTION print_bt (str IN VARCHAR2) RETURN VARCHAR2 IS bt subject_t; BEGIN IF SELF.broader_term_ref IS NULL THEN RETURN str; ELSE UTL_REF.SELECT_OBJECT(SELF.broader_term_ref, bt); RETURN bt.print_bt(NVL(str,SELF.name)) || ' (' || bt.name || ')'; END IF; END;

This recursive procedure walks the hierarchy from the current subject to the "topmost" broader subject.

In C, Better Support for REFs While PL/SQL offers few overwhelming reasons to program with object references, you would find more benefits to this programming style with the Oracle Call Interface (OCI), Oracle's C/C++ language interface, or even with Pro*C. In addition to the ability to navigate REFs, similar to what you find in PL/SQL, OCI provides complex object retrieval (COR). With COR, you can retrieve an object and all its REFerenced neighbors in a single call. Both OCI and Pro*C support a client-side object cache, allowing an application to load objects into client memory and to manipulate (select, insert, update, delete) them as if they were in the database. Then, in a single call, the application can flush all the changes back to the server. In addition to improving the programmer's functional repertoire, these features reduce the number of network round trips, improving overall performance.

When using the procedures in UTL_REF, the REF argument you supply must be typed to match your object argument. The complete list of subprograms in UTL_REF follows:

UTL_REF.SELECT_OBJECT ( obj_ref IN, object_variable OUT);

Finds the object to which obj_ref points and retrieves a copy in object_variable.

UTL_REF.SELECT_OBJECT_WITH_CR ( obj_ref IN, object_variable OUT);

Like SELECT_OBJECT, but makes a copy ("snapshot") of the object. This version exists to avoid a mutating table error (ORA-4091), which can occur if you are updating an object table and setting the value to a function, but the function uses UTL_REF to dereference an object from the same table you're updating.

UTL_REF.LOCK_OBJECT ( obj_ref IN);

Locks the object to which obj_ref points but does not fetch it yet.

UTL_REF.LOCK_OBJECT ( obj_ref IN, object_variable OUT);

Locks the object to which obj_ref points and retrieves a copy in object_variable.

UTL_REF.UPDATE_OBJECT ( obj_ref IN, object_variable IN);

Replaces the object to which obj_ref points with the value supplied in object_variable. This operation updates all of the columns in the corresponding object table.

UTL_REF.DELETE_OBJECT ( obj_ref IN);

Deletes the object to which obj_ref points.