Oracle Oerr Utility Utilities

         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 this tool.

 

%oerr

Usage: oerr facility error

 

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 the error.  So type "oerr ora 7300". If one gets LCD-111, type "oerr lcd 111", and so on.

DBMS_PROFILER

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.

DBMS_DEBUG

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 plsql_debug=true;

set serveroutput on

var x varchar2(50)

begin

  :x := dbms_debug.initialize();

  dbms_debug.debug_on();

end;

/

print x

 

begin

  do_whatever;

end;

/

 

In session two:

 

set serveroutput on

exec dbms_debug.attach_session('&ssid')

 

When done, turn off debugging and in session two, detach. The output is then available for viewing.

Summary

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 executables.

 

In general, remember that utilities come in the following forms:

• Built-in packages

• Command line executable

• External programs, from Oracle or not

Sage advice in construction applies here as well:  use 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.

 

 

For 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 Rampant TechPress.