Quick Search:
CODE
Oracle PL/SQL Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
 The Oracle UTL_REF Package      [Return To Index] Jump to:  
  Looking for the original pages? (formerly called "Morgan's Library") You can find them here.

Term: UTL_REF

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.

 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us 282 users online    © 2009 psoug.org
PSOUG LOGIN
Username: 
Password: 
Forgot your password?