Oracle UTL_REF Object


	CODE Oracle Code Library


	JOBS Find Or Post Oracle Jobs


	FORUM Oracle Discussion & Chat

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