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.
DECLARE
--both are of the same type - BOOK_t
the_book_ref REF BOOK_t;
the_book
BOOK_t;
BEGIN
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);
END;
/
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).
UTL_SMTP
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.
CREATE OR REPLACE PROCEDURE
SEND_MAIL (
msg_to varchar2,
msg_subject varchar2,
msg_text varchar2 )
IS
c utl_smtp.connection;
rc integer;
msg_from varchar2(50) := 'Oracle9.2';
mailhost
VARCHAR2(30) := '127.0.0.1'; -- local database host
BEGIN
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);
utl_smtp.data(c,'From: Oracle
Database' || utl_tcp.crlf ||
'To: ' || msg_to || utl_tcp.crlf
||
'Subject: ' || msg_subject ||
utl_tcp.crlf || msg_text);
utl_smtp.quit(c);
EXCEPTION
WHEN
UTL_SMTP.INVALID_OPERATION THEN
dbms_output.put_line(' Invalid
Operation in Mail attempt
using UTL_SMTP.');
WHEN
UTL_SMTP.TRANSIENT_ERROR THEN
dbms_output.put_line(' Temporary
e-mail issue - try again');
WHEN UTL_SMTP.PERMANENT_ERROR THEN
dbms_output.put_line(' Permanent Error Encountered.');
END;
/
and include the URL for the page.
