Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Jun 23, 2005

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

By Jim Czuprynski

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.


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

Oracle Archives

Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM