Oracle UTL_REF
Version 12.1.0.2

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.
Purpose API supporting reference (REF) based operations. Unlike SQL, UTL_REF procedures enable writing generic type methods without knowing the object table name.
AUTHID DEFINER
Dependencies
DBMS_UTL_REF_LIB    
Documented Yes
Exceptions
Error Code Reason
ORA-00060 Deadlock detected
ORA-00942 Insufficient privileges
ORA-01031 Insufficient privileges
ORA-01403 No data found (REF is NULL)
ORA-08177 Unable to serialize a serializable transaction
First Available 8.0
Security Model Owned by SYS with EXECUTE granted to PUBLIC
Source {ORACLE_HOME}/rdbms/admin/utlref.plb
Subprograms
 
DELETE_OBJECT
Deletes an object given a reference utl_ref.delete_object(reference IN REF "<typename>");
conn uwclass/uwclass@pdbdev

CREATE OR REPLACE TYPE address_t AS OBJECT (
add_typ VARCHAR2(1),
address VARCHAR2(40),
zip     VARCHAR2(5),
phone   VARCHAR2(12));
/

CREATE OR REPLACE TYPE person_t AS OBJECT (
person_id  NUMBER(5),
first_name VARCHAR2(30),
last_name  VARCHAR2(30),
address    address_t);
/

CREATE TABLE employee (
emp_id   NUMBER(5),
person   person_t,
hiredate DATE);

INSERT INTO employee
(emp_id, person, hiredate)
VALUES
(1, person_t(100, 'Daniel','Morgan',
address_t('H','PO Box 521','98040','206-669-2949')), SYSDATE);

COMMIT;

SELECT * FROM employee;

DECLARE
 refvar person_t;
 objout address_t;
BEGIN
  utl_ref.lock_object(refvar, objout);
END;
/

CREATE OR REPLACE TYPE person_t AS OBJECT (
person_id  NUMBER(5),
first_name VARCHAR2(30),
last_name  VARCHAR2(30),
address    address_t,
MEMBER PROCEDURE setAddress(addr IN address_t));
/

CREATE OR REPLACE TYPE BODY person_t IS
MEMBER PROCEDURE setAddress(addr address_t) IS
  BEGIN
    address := addr;
  END;
END;
/

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

CREATE OR REPLACE TYPE employee_t AS OBJECT (
thePerson  REF person_t,
empno      NUMBER(5),
deptREF    department_t,
mgrREF     person_t,  -- was employee_t
MEMBER PROCEDURE setAddress(addr IN address_t));
/

CREATE OR REPLACE TYPE BODY Employee_t IS
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);

  -- eelegate 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
    NULL;
    -- errmsg := SUBStr(SQLERRM, 1, 200);
  END;
END;
/
 
LOCK_OBJECT
Locks an object given a reference
Overload 1
utl_ref.lock_object(reference IN REF "<typename>");
See DELETE_OBJECT Demo Above
Overload 2 UTL_REF.LOCK_OBJECT (
reference IN REF "<typename>",
object    IN OUT "<typename>")
See DELETE_OBJECT Demo Above
 
SELECT_OBJECT
Selects from an object given a reference utl_ref.select_object(
reference IN REF "<typename>",
object    IN OUT "<typename>");
See DELETE_OBJECT Demo Above
 
SELECT_OBJECT_WITH_CR
Undocumented utl_ref.select_object_with_cr(
reference IN REF "<typename>",
object    IN OUT "<typename>");
TBD
 
UPDATE_OBJECT
Updates an object given a reference utl_ref.update_object(
reference IN REF "<typename>",
object    IN     "<typename>");
See DELETE_OBJECT Demo Above

Related Topics
Nested Tables
Object Table Constraints
Packages
Types

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved