Oracle UTL_REF
Version 12.2.0.1

General Information
Library Note Morgan's Library Page Header
"I spent my entire adult life looking out for the well-being, the training, the equipping of the troops for whom I was responsible. I will not be lectured about what our military needs by a five-deferment draft dodger,"
~ Sen. Tammy Duckworth
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);

  -- 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
    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
Built-in Functions
Built-in Packages
Nested Tables
Object Table Constraints
Types
What's New In 12cR1
What's New In 12cR2

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-2017 Daniel A. Morgan All Rights Reserved