Oracle Built-in Packages

UTL_REF: Referencing Objects (Oracle8.0.4)

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 ...