Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 







Oracle UTL_REF Package

Oracle Database Tips by Donald Burleson

Advanced Oracle Utilities: The Definitive Reference by Rampant TechPress is written by top Oracle database experts (Bert Scalzo, Donald Burleson, and Steve Callan).  The following is an excerpt from the book.

The UTL_REF package is a case where the difference between definer and invoker rights matters. The security model comes into play depending on where (server versus client) the package is being called from. If on the server, it is invoker rights. If from a client, then definer rights rule. Normally, calls on the server operate with definer privileges and the owner of the package must have privileges on the underlying objects.


What is UTL_REF used for? If an object has a reference, then select that object. Somewhat like a locator, which was mentioned earlier, a row of an object table stores an object. How does one reference that object? Since the developer can use object identifiers to uniquely identify an object, a persistent pointer, or reference, can be based upon that object identifier. Coming back full circle, if there is a reference, then an object can be obtained.


The subprograms in UTL_REF include operation for deleting, locking, selecting, and updating an object. Locking an object implicitly selects it. In fact, the LOCK_OBJECT procedure is analogous to issuing a SELECT…FOR UPDATE in terms of locking a record, but in this case, an object will be locked upon.


With a reference, there is no need to know the object table name. That is the main benefit of using UTL_REF. Once the object ID is identified, i.e., the reference to it, an update can be performed or deleted on it.


Most examples use addresses or some other collection such as books, records, CDs, or animals. Here is an example using a library, or collection of books, and the use of LOCK and UPDATE.



  --both are of the same type - BOOK_t

  the_book_ref REF BOOK_t;

  the_book         BOOK_t;


  select REF(book)

  into the_book_ref

  from library book

  where author = 'Jones'

  and title = 'Dark Night';


  --parameters are reference and object

  --object is the same object type as the locked object

  UTL_REF.LOCK_OBJECT(the_book_ref, the_book);

  --change the title

  the_book.title := 'Stormy Night';

  --same rule applies for the lock operation

  UTL_REF.UPDATE_OBJECT(the_book_ref, the_book);




Abstract data types (ADT), user-defined types (UDT) and object-oriented implementation within Oracle always seems awkward to use. This is an area where practice and repetition help to make the syntax more palatable. For the majority of database administrators and developers, use of nested tables and object types will be a rare event.


With respect to REF type items, what is more common is the use of REF cursors. REF cursors are discussed in detail in Oracle PL/SQL Tuning, Expert Secrets for High Performance Programming (Rampant TechPress, 2006).


This package was introduced several versions ago. It is complex and cumbersome to use. If this package is already being used, there is probably very little new to add to what one has been using for quite a long time. If one is new to sending email from Oracle, see UTL_MAIL. It is much simpler and easier to implement. Although still supported in newer releases, it may be worthwhile to consider moving from UTL_SMTP to UTL_MAIL. Any deprecation of UTL_SMTP would have to be announced well before that takes place due to the number of legacy applications using this package. This utility is also covered in Chapter 6 from the DBA's point of view.


Here is a sample implementation of UTL_SMTP.


msg_to varchar2,
msg_subject varchar2,
msg_text varchar2 )
c utl_smtp.connection;
rc integer;
msg_from varchar2(50) := 'Oracle9.2';
mailhost VARCHAR2(30) := ''; -- local database host

c := utl_smtp.open_connection(mailhost, 25); -- SMTP on port 25
utl_smtp.helo(c, mailhost);
utl_smtp.mail(c, msg_from);
utl_smtp.rcpt(c, msg_to);,'From: Oracle Database' || utl_tcp.crlf ||
'To: ' || msg_to || utl_tcp.crlf ||
'Subject: ' || msg_subject ||
utl_tcp.crlf || msg_text);

dbms_output.put_line(' Invalid Operation in Mail attempt
using UTL_SMTP.');
dbms_output.put_line(' Temporary e-mail issue - try again');
dbms_output.put_line(' Permanent Error Encountered.');



Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.