Definition:
In Oracle PL/SQL, UTL_REF is a built in package which supports reference based operations in the Oracle object model. It is created by the DBA and executed by the users. When a user executes the UTL_REF package for REF object operation, it checks for the object access privileges. In case of dangling objects or locked objects, it raises an exception.
The UTL_REF subprograms are listed below.
- DELETE_OBJECT Procedure - Deletes an object given a reference
- LOCK_OBJECT Procedure - Locks an object given a reference
- SELECT_OBJECT Procedure - Selects an object given a reference
- UPDATE_OBJECT Procedure - Updates an object given a reference
Example Syntax:
UTL_REF.DELETE_OBJECT ( reference IN REF "");
UTL_REF.LOCK_OBJECT ( reference IN REF "", object IN OUT "");
UTL_REF.LOCK_OBJECT ( reference IN REF "");
UTL_REF.SELECT_OBJECT (reference IN REF "", object IN OUT "");
UTL_REF.UPDATE_OBJECT ( reference IN REF "", object IN "");
Example Usage:
The example code below declares schema object types and uses UTL_REF to establish a reference between its attribute and object type instance.
CREATE OR REPLACE TYPE OT_STUDENT AS OBJECT
( ROLLNO NUMBER,
NAME VARCHAR2(100)
);
Type created.
CREATE OR REPLACE TYPE OT_SESSION AS OBJECT
( STUDENT OT_STUDENT,
SUBJECT VARCHAR2(100),
MEMBER PROCEDURE SET_STUDENT(L_STUD in OT_STUDENT)
);
Type created.
CREATE OR REPLACE TYPE BODY OT_SESSION
AS
MEMBER PROCEDURE SET_STUDENT(L_STUD IN OT_STUDENT) IS
BEGIN
student := l_stud;
END;
END;
Type body created.
CREATE OR REPLACE TYPE OT_SCHOOL AS OBJECT
(NAME VARCHAR2(100),
EXAMS REF OT_SESSION,
MEMBER PROCEDURE SET_STUDENT(L_STUD IN OT_STUDENT)
);
Type created.
CREATE OR REPLACE TYPE BODY OT_SCHOOL AS
MEMBER PROCEDURE SET_STUDENT(L_STUD IN OT_STUDENT)
IS
M_STUDENT OT_SESSION;
BEGIN
UTL_REF.LOCK_OBJECT(EXAMS,M_STUDENT);
M_STUDENT.SET_STUDENT(L_STUD);
UTL_REF.UPDATE_OBJECT(EXAMS,M_STUDENT);
END;
END;
Type body created.