CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle UTL_REF
Version 11.1
 
General Information
Source {ORACLE_HOME}/rdbms/admin/utlref.plb
First Available 8.0
Dependencies DBMS_UTL_REF_LIB
Exceptions
Error Code Reason
00060 Deadlock detected
00942 Insufficient privileges
01031 Insufficient privileges
01403 No data found (REF is NULL)
08177 Unable to serialize a serializable transaction
Security Model Execute is granted to PUBLIC
 
DELETE_OBJECT

Deletes an object given a reference
utl_ref.delete_object(reference IN REF "<typename>");
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
Overload 2 UTL_REF.LOCK_OBJECT (
reference IN REF "<typename>", 
object    IN OUT "<typename>"); 
See DELETE_OBJECT demo
 
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
 
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
 
Related Topics
Types
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [332 users online]    © 2010 psoug.org