The UTL_REF package is created when the Oracle8.0.4 (or later) database is installed. The utlref.sql script (found in the built-in packages source code directory, as described in Chapter 1) contains the source code for this package’s specification. The script is called by catproc.sql, which is normally run immediately after the database is created. The script creates the public synonym UTL_REF for the package and grants EXECUTE privilege on the package to public. All Oracle users can reference and make use of the package.

Every program in this package runs as “owner.” This means that programs in the UTL_REF package operate within the privileges of the session running those programs. You will be able to select and modify only objects to which your session has been granted the necessary privileges.

This section describes the programs available through the UTL_REF package. A single, extended example at the end of the chapter shows how you might be able to take advantage of the UTL_REF programs in your own applications.

PROCEDURE UTL_REF.DELETE_(reference IN REF ANY);

DELETE FROM the_underlying_object_table t
 WHERE REF (t) = reference;

PROCEDURE UTL_REF.LOCK_OBJECT (reference IN REF ANY);

PROCEDURE UTL_REF.LOCK_OBJECT 
   (reference IN REF ANY
   ,object IN OUT ANY);

SELECT VALUE (t)
  INTO object
  FROM the_underlying_object_table t
 WHERE REF (t) = reference
   FOR UPDATE;

PROCEDURE UTL_REF.SELECT_OBJECT 
   (reference IN REF ANY
   ,object IN OUT ANY);

SELECT VALUE (t)
  INTO object
  FROM the_underlying_object_table t
 WHERE REF (t) = reference;

CREATE OR REPLACE PROCEDURE show_emp (emp_in IN REF employee_t)
IS
   emp_obj employee_t
BEGIN
   UTL_REF.SELECT_OBJECT (emp_in, emp_obj);
   DBMS_OUTPUT.PUT_LINE (emp_obj.name);
END;.

PROCEDURE UTL_REF.UPDATE_OBJECT 
   (reference IN REF ANY
   ,object IN ANY);

UPDATE the_underlying_object_table t 
   SET VALUE (t) = object
 WHERE REF (t) = reference;

Let’s start with an object type that can hold various types of documents

CREATE OR REPLACE TYPE Document_t AS OBJECT (
   doc_id NUMBER,
   author VARCHAR2(65),
   created DATE,
   revised DATE,
   body BLOB,
   MEMBER PROCEDURE update_revised
);
/

To keep this example simple, we’ll implement only a single object method:

CREATE OR REPLACE TYPE BODY Document_t
AS
   MEMBER PROCEDURE update_revised
   IS
   BEGIN
      revised := SYSDATE;
   END;
END;
/

Here’s a table that will hold any kind of document:

CREATE TABLE documents OF Document_t;

We might have a requisition type that has a special type of document. Each requisition contains a REF to a particular document.

CREATE OR REPLACE TYPE Requisition_t AS OBJECT (
   doc_ref REF Document_t,
   needed DATE,
   approved DATE,
   MEMBER PROCEDURE update_revision_date,
   MEMBER FUNCTION has_valid_need_date RETURN BOOLEAN
);
/

In a moment, we’re going to look at an example of UTL_REF that implements the type body of Requisition_t. But let’s first look at life without UTL_REF. Not only do we have to write SQL, we also have to know the table name in each statement where we need access to a persistent object. In fact, the following methods are hard-coded to work with only one particular table implementation (not good):

CREATE OR REPLACE TYPE BODY Requisition_t
AS
   MEMBER FUNCTION has_valid_need_date RETURN BOOLEAN
   IS
      document Document_t;
      CURSOR doc_cur IS                /* Ugly! */
         SELECT VALUE(d) 
           FROM documents d
          WHERE REF(d) = SELF.doc_ref;
   BEGIN
      OPEN doc_cur;
      FETCH doc_cur INTO document;    /* Ditto */
      CLOSE doc_cur;
      IF document.created > SELF.approved
      THEN
         RETURN FALSE;
      ELSE
         RETURN TRUE;
      END IF;
   END;
   MEMBER PROCEDURE update_revision_date
   IS
   BEGIN
      UPDATE documents d            /* Even uglier */
         SET revised = SYSDATE
       WHERE REF(d) = SELF.doc_ref;
   END;
END;
/

Let’s turn now to see what UTL_REF can do for us:

CREATE OR REPLACE TYPE BODY Requisition_t
AS
   MEMBER FUNCTION has_valid_need_date RETURN BOOLEAN
   IS
      document Document_t;
   BEGIN
      /* UTL_REF.SELECT_OBJECT allows us to retrieve the document object 
      || from persistent database storage into a local variable.  No muss, 
      || no fetch, no bother!  SELECT_OBJECT finds the table and object 
      || for us.
      */
      UTL_REF.SELECT_OBJECT (SELF.doc_ref, document);
      /* Now that we have retrieved the document object, we can
      || easily gain access to its attributes:
      */
      IF document.created > SELF.approved
      THEN
         RETURN FALSE;
      ELSE
         RETURN TRUE;
      END IF;
   END;
   MEMBER PROCEDURE update_revision_date
   IS
      document Document_t;
   BEGIN
      /* To update the revision date of the requisition object,
      || we'll simply "delegate" to the referenced document.
      || First we retrieve it...
      */
      UTL_REF.SELECT_OBJECT (SELF.doc_ref, document);
      /* ...then we can invoke a method on the newly retrieved
      || (but transient) object.  Notice that we do NOT update
      || the attribute directly, but rely instead on the public
      || method supplied for this purpose.
      */
      document.update_revised;
      /* ...and now we easily update the data in the underlying table
      || (whatever table it is...we don't know or care!)
      */
      UTL_REF.UPDATE_OBJECT(SELF.doc_ref, document);
   END;
END;
/

Since UTL_REF frees us from dependence on the specific underlying table, it allows us to achieve greater reuse, portability, modularity, and resilience to change.