99
UTL_REF

Oracle8i supports user-defined composite type or object type. Any instance of an object type is called an object. An object type can be used as the type of a column or as the type of a table.

In an object table, each row of the table stores an object. You can uniquely identify an object in an object table with an object identifier.

A reference is a persistent pointer to an object, and each reference can contain an object identifier. The reference can be an attribute of an object type, or it can be stored in a column of a table. Given a reference, an object can be retrieved.

The UTL_REF package provides PL/SQL procedures to support reference-based operations. Unlike SQL, UTL_REF procedures enable you to write generic type methods without knowing the object table name.

This chapter discusses the following topics:

• Requirements
• Datatypes, Exceptions, and Security for UTL_REF
• Summary of UTL_REF Subprograms

Requirements

The procedural option is needed to use this package. This package must be created under SYS (connect/as sysdba). Operations provided by this package are performed under the current calling user, not under the package owner SYS.

Datatypes, Exceptions, and Security for UTL_REF

Datatypes

An object type is a composite datatype defined by the user or supplied as a library type. You can create the object type employee_type using the following syntax:

CREATE TYPE employee_type AS OBJECT (
   name    VARCHAR2(20),
   id      NUMBER,

member function GET_ID
    (name VARCHAR2) 
   RETURN MEMBER);

The object type employee_type is a user-defined type that contains two attributes, name and id, and a member function, GET_ID().

You can create an object table using the following SQL syntax:

CREATE TABLE employee_table OF employee_type;

Exceptions

Exceptions can be returned during execution of UTL_REF functions for various reasons. For example, the following scenarios would result in exceptions:

• The object selected does not exist. This could be because either:
  1. The object has been deleted, or the given reference is dangling (invalid).
  2. The object table was dropped or does not exist.
• The object cannot be modified or locked in a serializable transaction. The object was modified by another transaction after the serializable transaction started.
• You do not have the privilege to select or modify the object. The caller of the UTL_REF subprogram must have the proper privilege on the object that is being selected or modified.

  Table 99-1 UTL_REF Exceptions

  Exceptions	Description
  errnum == 942	Insufficient privileges.
  errnum == 1031	Insufficient privileges.
  errnum == 8177	Unable to serialize, if in a serializable transaction.
  errnum == 60	Deadlock detected.
  errnum == 1403	No data found (if the REF is null, etc.).

The UTL_REF package does not define any named exceptions. You may define exception handling blocks to catch specific exceptions and to handle them appropriately.

Security

You can use the UTL_REF package from stored PL/SQL procedures/packages on the server, as well as from client/side PL/SQL code.

When invoked from PL/SQL procedures/packages on the server, UTL_REF verifies that the invoker has the appropriate privileges to access the object pointed to by the REF.

Thus, if UTL_REF is defined under user SYS, and user A invokes UTL_REF.SELECT to select an object from a reference, then user A (the invoker) requires the privileges to check.

When invoked from client-side PL/SQL code, UTL_REF operates with the privileges of the client session under which the PL/SQL execution is being done.