The oerr utility (command line executable) can
be used to look up Oracle error messages. Many
products within Oracle contain an msg file, or
message library. Available only on UNIX
platforms and not on Windows, type oerr at the
command prompt to receive help on how to use
Usage: oerr facility
Facility is identified by the three-letter
prefix in the error string.
For example, if the developer gets
ORA-7300, "ora" is the facility and "7300" is
So type "oerr ora 7300". If one gets
LCD-111, type "oerr lcd 111", and so on.
This package can be used to capture time spent
on PL/SQL calls. Tracing helps find wait times
for SQL, but what about time spent on PL/SQL?
The steps to run DBMS_PROFILER are easy to
perform. Prior to running, get the latest
version of the source code from MetaLink. See
Note 243775.1, “Implementing and Using the
PL.SQL Profiler” and download the PROF.zip file.
Once the files have been downloaded and
extracted, take a look at profiler_7.html and
see if this utility does not impress with what
it can do. The number of times a command or
instruction was executed and the time spent
doing it are readily seen in the HTML-formatted
output. Best of all, this tool is free.
For Forms developers, running debug in a Forms
session is made easy because of the GUI
interface and modal windows inside Forms
Builder. In regular PL/SQL on the command line,
the same is not true. Although DBMS_DEBUG can
provide pretty much the same output as what is
seen in Forms debugging, the overhead of running
the debugger is somewhat problematic. MetaLink
note 221346.1, “DBMS_DEBUG: Simple Example of
Debugging An Anonymous Block” offers a fairly
simple example of using the package.
The basic steps are to run two sessions. In the
first, initialize debug, obtain an identifier,
and call the code. A second session is then
attached to the first using the identifier. So,
in session one:
alter session set
set serveroutput on
var x varchar2(50)
In session two:
set serveroutput on
When done, turn off debugging and in session
two, detach. The output is then available for
From release to release, the number of built-in
packages within Oracle has shown a steady
increase over the past ten years. It would be
safe to assume this trend will continue. What
defines a package as being a utility is mostly
left to the interpretation of the user. The name
of a package does not always belie its function.
Do not think that only UTL packages are utility
related. As shown in this chapter, utility-like
tools can be named DBMS and even be command line
In general, remember that utilities come in the
Sage advice in construction applies here as
the right tool for the job at hand. There are
certainly plenty to choose from, so make sure
the task is not being made harder when a simpler
utility would have sufficed.
r more details on Oracle utilities, see the book "Advanced
Oracle Utilities" by Bert Scalzo, Donald K. Burleson, and Steve Callan.
You can buy it direct from the publisher for 30% off directly from