UTL_REF , 2 of 2

Summary of Subprograms

Table 64-2 UTL_REF Subprograms

Subprogram	Description
SELECT_OBJECT Procedure	Selects an object given a reference.
LOCK_OBJECT Procedure	Locks an object given a reference.
UPDATE_OBJECT Procedure	Updates an object given a reference.
DELETE_OBJECT Procedure	Deletes an object given a reference.

SELECT_OBJECT Procedure

This procedure selects an object given its reference. The selected object is retrieved from the database and its value is put into the PL/SQL variable 'object'. The semantic of this subprogram is similar to the following SQL statement:

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

Unlike the above SQL statement, this subprogram does not require you to specify the object table name where the object resides.

Syntax

UTL_REF.SELECT_OBJECT (
   reference IN REF "<typename>", 
   object    IN OUT "<typename>");

Parameters

Table 64-3 SELECT_OBJECT Procedure Parameters

Parameter	Description
reference	Reference to the object to select or retrieve.
object	The PL/SQL variable that stores the selected object; this variable should be of the same object type as the referenced object.

Returns

None.

Pragmas

None.

Exceptions

May be raised.

LOCK_OBJECT Procedure

This procedure locks an object given a reference. In addition, this procedure lets the program select the locked object. The semantic of this subprogram is similar to the following SQL statement:

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

Unlike the above SQL statement, this subprogram does not require you to specify the object table name where the object resides. It is not necessary to lock an object before updating/deleting it.

Syntax

UTL_REF.LOCK_OBJECT (
   reference IN REF "<typename>"); 

UTL_REF.LOCK_OBJECT (
   reference IN REF "<typename>", 
   object    IN OUT "<typename>");

Parameters

Table 64-4 LOCK_OBJECT Procedure Parameters

Parameter	Description
reference	Reference of the object to lock.
object	The PL/SQL variable that stores the locked object. This variable should be of the same object type as the locked object.

Returns

None.

Pragmas

None.

Exceptions

May be raised.

UPDATE_OBJECT Procedure

This procedure updates an object given a reference. The referenced object is updated with the value contained in the PL/SQL variable 'object'. The semantic of this subprogram is similar to the following SQL statement:

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

Unlike the above SQL statement, this subprogram does not require you to specify the object table name where the object resides.

Syntax

UTL_REF.UPDATE_OBJECT (
   reference IN REF "<typename>", 
   object    IN     "<typename>");

Parameters

Table 64-5 UPDATE_OBJECT Procedure Parameters

Parameter	Description
reference	Reference of the object to update.
object	The PL/SQL variable that contains the new value of the object. This variable should be of the same object type as the object to update.

Returns

None.

Pragmas

None.

Exceptions

May be raised.

DELETE_OBJECT Procedure

This procedure deletes an object given a reference. The semantic of this subprogram is similar to the following SQL statement:

DELETE FROM object_table  
WHERE REF(t) = reference;

Unlike the above SQL statement, this subprogram does not require you to specify the object table name where the object resides.

Syntax

UTL_REF.DELETE_OBJECT (
   reference IN REF "<typename>");

Parameters

Table 64-6 DELETE_OBJECT Procedure Parameters

Parameter	Description
reference	Reference of the object to delete.

Returns

None.

Pragmas

None.

Exceptions

May be raised.

Example

The following example illustrates usage of the UTL_REF package to implement this scenario: if an employee of a company changes their address, their manager should be notified.

... declarations of Address_t and others...

CREATE OR REPLACE TYPE Person_t ( 

name    VARCHAR2(64), 
gender  CHAR(1), 
address Address_t, 
MEMBER PROCEDURE setAddress(addr IN Address_t) 

); 

CREATE OR REPLACE TYPE BODY Person_t ( 

MEMBER PROCEDURE setAddress(addr IN Address_t) IS 
BEGIN 


address := addr; 


END; 

); 

CREATE OR REPLACE TYPE Employee_t (

Under Person_t: Simulate implementation of inheritance using a REF to Person_t and delegation of setAddress to it.

thePerson  REF Person_t, 
empno      NUMBER(5), 
deptREF    Department_t, 
mgrREF     Employee_t, 
reminders  StringArray_t, 
MEMBER PROCEDURE setAddress(addr IN Address_t), 
MEMBER procedure addReminder(reminder VARCHAR2);

); 

CREATE TYPE BODY Employee_t ( 

MEMBER PROCEDURE setAddress(addr IN Address_t) IS 


myMgr Employee_t; 
meAsPerson Person_t; 


BEGIN

Update the address by delegating the responsibility to thePerson. Lock the Person object from the reference, and also select it:

UTL_REF.LOCK_OBJECT(thePerson, meAsPerson); 
meAsPerson.setAddress(addr);

Delegate to thePerson:

        UTL_REF.UPDATE_OBJECT(thePerson, meAsPerson); 

if mgr is NOT NULL THEN

Give the manager a reminder:

UTL_REF.LOCK_OBJECT(mgr); 
UTL_REF.SELECT_OBJECT(mgr, myMgr); 
myMgr.addReminder 
('Update address in the employee directory for' || 
thePerson.name || ', new address: ' || addr.asString); 
UTL_REF.UPDATE_OBJECT(mgr, myMgr);

END IF;

EXCEPTION

WHEN OTHERS THEN 
errnum := SQLCODE; 
errmsg := SUBSTR(SQLERRM, 1, 200);