Oracle 10g PL/SQL Enhancements, Part 2: Utility Players

Synopsis. Oracle 10g has added over fifty new
PL/SQL packages and enhanced many of the existing packages, thus expanding the
Oracle DBA’s toolkit once again. New capabilities include the ability to transfer
files between servers, more robust e-mail features, improved compilation utilities,
and character conversion utilities. This article – the final one in this series
– delves into several new features presented in new and improved versions of
Oracle’s utility modules and illustrates their usefulness in some real-world
situations.

The previous
article
in this series looked at some of the new PL/SQL packages that
Oracle 10g now provides for encrypting and decrypting data, monitoring sessions,
and triggering alerts when thresholds have been breached. Rounding out these
new and improved features are several packages that I like to think of as
“utility players”: quietly reliable tools that handle important jobs and are
welcome additions to any Oracle DBA’s toolbelt. I will start with one of the
more de rigeur tasks every Oracle DBA faces on a regular basis: object
recompilation.

Recompiling Invalidated Objects with UTL_RECOMP

Compiling database objects can be a heavy drain on even
the most robust server. I am sure you have noticed the same symptoms that I
have, especially during major conversion efforts like database upgrades from
prior versions: CPU utilization on less-powerful servers spiking at nearly 100%
for several minutes.

The good news is that the new UTL_RECOMP package will recompile all invalidated
objects for the entire database or for just a specified schema. Even
better, UTL_RECOMP
also adds the ability to recompile invalidated objects either sequentially
(i.e. following the chain of invalidated objects) or in parallel using a
specified number of threads – a particularly useful option for servers with
multiple CPUs.

Oracle recommends that the number of threads used for
parallel recompilation be limited to one per CPU. UTL_RECOMP also must be executed from within a
SQL*Plus session, and that session must be connected to the database as the
SYSDBA role. Listing
2.1
shows several examples of how to apply these new features to these
mundane but critical tasks.

Conversion Utilities Galore: UTL_I18N

I introduced the new UTL_I18N package briefly in my previous article
in the examples
for DBMS_CRYTPO.
As that example illustrated, UTL_I18N
provides methods for conversion of string data from VARCHAR TO RAW and back
again, with the added benefit of automatic conversion within either the
database or national character sets. However, this package’s real intent is to
make life easier for Oracle developers who want to build “internationalized,”
multi-lingual applications.

Listing
2.2
explores this new package’s character conversion functions,
including examples of translating a RAW value to either a VARCHAR2 or NVARCHAR2
character while specifying the desired character set.

UTL_I18N also
provides features to help developers create applications that are truly internationalized.
For example, the GET_DEFAULT_CHARSET
function returns the “safe” character set for different languages depending on
the context in which the application will be used (i.e. either generic text or
e-mail mode) and within which operating system (i.e. Windows NT or
non-Windows).

UTL_I18N also
provides functions for determining which ISO character sets correspond to which
Oracle character sets, and vice versa. Finally, it provides methods for
translating the Oracle National Language Services (NLS) designations for language
and territory (e.g. AMERICA.AMERICAN) to the corresponding ISO locale.

Listing
2.3
shows an example of how to use this function to return the safest
character set for Spanish generic text under Linux, Italian e-mail text under
Windows, and French generic text under UNIX. I have also provided examples of
the ISO – Oracle character set and locale translation functions.

External File Utilities: UTL_FILE

The UTL_FILE package has long been a stalwart, flexible
tool on my toolbelt. I have used it for writing log files and reading from
external files. Long before DBMS_METADATA came along, I have even used it to
create standardized, formatted dumps of my database’s schema for archival and
comparison purposes. About the only sore spot I have encountered is the need to
restart the database whenever a new destination directory needed to be added
via the UTL_FILE_DIR initialization parameter.

Oracle 10g eliminates the need to add a new directory to
the initialization parameter file with its full embrace of the DIRECTORY
object for UTL_FILE. Listing
2.4
shows how to create a new DIRECTORY object, how to read from an external
file, and how to write to an external file, and how to read back that same
external file with UTL_FILE.

Transferring Binary Files Between Servers with DBMS_FILE_TRANSFER

Oracle 10g’s myriad improvements in file handling are
especially encouraging, particularly for those of us whose careers span the
mainframe, client-server, and grid computing revolutions. I have to admit that
I really miss the standard utilities those old “big iron” mainframes presented
for moving huge sequential files (aka “flat files”) between systems and
servers.

The new DBMS_FILE_TRANSFER
package gives an Oracle DBA the power to copy a binary file from one
directory
on a local server with a new file name in the same
directory, or to a different directory with the same file name on
the local server. Even better, a binary file can be copied from a
directory on a remote database server to a directory on the local
database server. Finally, a binary file can be copied from a directory on the local
database server to a directory on a remote database server.

This package makes it easy to back up binary files
between directories or servers. Listing
2.5
shows each of the three scenarios listed previously to:

  • Back up an existing export file to a different directory on the
    same local server

  • Back up an existing export file to a directory on a remote server

  • Retrieve an existing export backup file from a directory on a
    remote server to the local server

Sending E-Mail and Attachments with UTL_MAIL

The new UTL_MAIL
package replaces most of the e-mail transmission functions of its predecessor, UTL_SMTP, and
adds some welcome enhancements. UTL_MAIL offers a streamlined, simpler, intuitive
interface to specify the e-mail’s sender, subject, message text, and
recipients, including the ability to add multiple e-mail addresses for carbon
copy (CC:)and blind carbon copy (BCC). In addition, it is now possible to
attach documents directly to an e-mail message in either VARCHAR2 or RAW
format.

First, it is important to note that the UTL_MAIL package is
not installed by default. It can be created by running two scripts, <ORACLE_HOME>/rdbms/admin/utlmail.sql
and <ORACLE_HOME>/rdbms/admin/prvtmail.sql,
from a SQL*Plus session. In addition, a new initialization parameter, SMTP_OUT_SERVER,
specifies the name of the mail server domain. Oracle does this because it gives
the DBA a chance to secure the port that SMTP_OUT_SERVER defines from being overloaded
from incoming data transmissions. If SMTP_OUT_SERVER is not defined, however, Oracle
automatically accepts the domain from the DB_DOMAIN initialization parameter instead.

Listing
2.6
shows three examples of sending an e-mail using UTL_MAIL. The
first example sends a simple e-mail without any attachments. The second example
sends another e-mail message, but this time includes an attachment in the
format of a RAW datatype. The final example sends a third e-mail message, but
includes an attachment in VARCHAR2 format.

Conclusion

Oracle 10g has sharpened and expanded the Oracle DBA’s
toolkit with new and improved PL/SQL packages that provide capabilities to
compile invalidated objects more quickly, transfer files between servers,
convert data into a myriad of internationalized formats, and send even more
flexible e-mails. This article has delved into several new features, but I
encourage you to explore even more deeply on your own – you will be pleasantly
surprised at the breadth of the enhancements!

References and Additional Reading

While there is no substitute for direct experience, reading
the manual is not a bad idea, either. I have drawn upon the following Oracle
10g documentation for the deeper technical details of this article:

B10750-01 Oracle Database
New Features Guide

B10802-01 PL/SQL Packages
and Types Reference

»


See All Articles by Columnist
Jim Czuprynski

Jim Czuprynski
Jim Czuprynski has accumulated over 30 years of experience during his information technology career. He has filled diverse roles at several Fortune 1000 companies in those three decades - mainframe programmer, applications developer, business analyst, and project manager - before becoming an Oracle database administrator in 2001. He currently holds OCP certification for Oracle 9i, 10g and 11g. Jim teaches the core Oracle University database administration courses on behalf of Oracle and its Education Partners throughout the United States and Canada, instructing several hundred Oracle DBAs since 2005. He was selected as Oracle Education Partner Instructor of the Year in 2009. Jim resides in Bartlett, Illinois, USA with his wife Ruth, whose career as a project manager and software quality assurance manager for a multinational insurance company makes for interesting marital discussions. He enjoys cross-country skiing, biking, bird watching, and writing about his life experiences in the field of information technology.

Latest Articles