Oracle Built-in Packages

Chapter 9: Datatype Packages

The UTL_REF package provides a PL/SQL interface that allows you to select and modify objects (instances of an object type) in an object table without having to specify or know about the underlying database table. With UTL_REF, you only need a reference to the object in order to identify it in the database and perform the desired operations. With UTL_REF, you can do any of the following:

• Select or retrieve an object from the database
• Lock an object so that no other session can make changes to the object
• Select and lock an object in a single operation (similar to SELECT FOR UPDATE)
• Update the contents of an object
• Delete an object

You will typically use UTL_REF programs when you have references to an object and one of the following is true:

• You do not want to have to resort to an SQL statement to perform the needed action.
• You do not even know the name of the table that contains the object, and therefore cannot rely on SQL to get your job done.

Before getting into the details, let's start with an initial example of how you might use the UTL_REF packages.

You will be able to use UTL_REF programs only to select or modify objects in an object table. An object table is a table in which each row of the table is an object. Here are the steps one might take to create an object table.

First, create an object type:

CREATE TYPE hazardous_site_t IS OBJECT (
name VARCHAR2(100),
location VARCHAR2(100),
dixoin_level NUMBER,
pcb_level NUMBER,
METHOD FUNCTION cleanup_time RETURN NUMBER);

Nowyou can create a table of these objects:

CREATE TABLE hazardous_sites OF hazardous_site_t;

As you will see in the headers for the UTL_REF programs, Oracle has provided a special parameter-passing syntax called ANY. This syntax allows us to pass references and objects of any object type in and out of the programs. This behavior is not otherwise available in Oracle8 built-in packages or the code that you yourself can write using object types.

Getting Started with UTL_REF

The UTL_REF package is created when the Oracle8.1 (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.

UTL_REF programs

Table 9-5 lists the programs defined for the UTL_REF packages.

UTL_REF does not declare any nonprogram elements.

UTL_REF exceptions

UTL_REF does not declare any exceptions. However, you may encounter any of the following Oracle exceptions when running the UTL_REF programs:

ORA-00942
Insufficient privileges. You must have the appropriate privileges on the under- lying database table.
ORA-01031
Insufficient privileges. You attempted to update an object table on which you have only SELECT privileges. You must have the appropriate privileges on the underlying database table.
ORA-08177
Cannot serialize access for this transaction. You have tried to change data after the start of a serialized transaction.
ORA-00060
Deadlock detected while waiting for resource. Your session and another ses- sion are waiting for a resource locked by the other. You will need to wait or ROLLBACK. ORA-01403
No data found. The REF is NULL or otherwise not associated with an object in the database.

UTL_REF Interface

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.

The UTL_REF.DELETE_OBJECT procedure
Use the DELETE_OBJECT procedure to delete an object (actually, the row containing that object) specified by the given reference. The header is,

PROCEDURE UTL_REF.DELETE_(reference IN REF ANY);

where reference identifies the object.

This program effectively substitutes for the following kind of SQL statement:

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

In contrast to this SQL statement, with DELETE_OBJECT you will not need to specify the name of the underlying database object table to retrieve the object....