| 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 |
|
