oerr

The oerr utility (Oracle Error) is provided only with Oracle databases on UNIX platforms. oerr is not an executable, but instead, a shell script that retrieves messages from installed message files. The utility is not provided on Windows systems, since it uses awk commands to retrieve the requested text from the file. However, later in this chapter, a Windows-based implementation will be provided.

The syntax required of the oerr utility is:

oerr <facility> <error>

The facility is the prefix to the error number. These include ORA, PLS, EXP, etc. The error is the actual error number returned by Oracle.

For example, if the database returned an ORA-12544 error, the following oerr command would be executed to gain more information:

$ oerr ora 12544

12544, 00000, "TNS:contexts have different wait/test functions"

// *Cause: Two protocol adapters have conflicting wait/test functions.

// *Action: Not normally visible to the user. For further details, turn

// on tracing and reexecute the operation. If error persists, contact

// Oracle Customer Support.

Notice that Oracle returned the “Cause” of the error and recommended “Action”. This is usually the case, however many error codes exist with no cause or action text available.

How oerr Works

To best understand exactly how oerr works, review the shell script. The oerr.ksh script is provided by Oracle in the $ORACLE_HOME/bin directory.

The commands in the oerr.ksh shell script confirm that $ORACLE_HOME is set, and if not, the program terminates. Next, the facility information is read from the facility file located in $ORACLE_HOME/lib/facility.lis. Below is a portion of the facility.lis file. The facility file contains three mandatory data items and one optional. The mandatory data items include the facility, component, and the name of the alias for the component, if one exists, otherwise a “*” will be used. In the file below, “ora” is the facility, “rdbms” is the component, and it does not have an alias “*”. The optional data item is the description, which none are provided in the file below.

Facility.lis file

o2u:precomp:*:

oae:oacore:*:

oao:office:*:

obk:obackup:ebu:

oci:rdbms:*:

ofc:office:*:

oma:office:*:

omapi:office:*:

omb:office:*:

omd:office:*:

omgut:office:*:

omkt:office:*:

omu:office:*:

omv:office:*:

opw:rdbms:*:

ora:rdbms:*:

osn:network:*:

osnq:network:*:

pcb:precomp:*:

pcbe:precomp:*:

pcc:precomp:*:

pcf:precomp:*:

pcg:precomp:*:

pci:precomp:*:

pcm:plsql:*:

pcp:precomp:*:

pcs:precomp:*:

pg2:precomp:*:

pgo:precomp:*:

pgp:precomp:*:

pls:plsql:*:

Figure 3.3 – oerr Architecture

Using the facility name that was entered on the command line, oerr retrieves the component for that facility (as displayed in Figure 3.3). In the example above (oerr ora 04030), oerr will use the rdbms component. Once the component is retrieved, oerr can derive the full path and file name for the appropriate message file:

Msg_File=$ORACLE_HOME/$Component/mesg/${Facility}us.msg

Following the example through, the line above equates to the following directory and file:

$ORACLE_HOME/rdbms/mesg/oraus.msg

Once the path and filename are determined, the contents of the file will provide the cause and action for each error in the facility:

01058, 00000, "internal New Upi interface error"

// *Cause: Attempt to delete non existant hstdef extension.

// *Action: Report as a bug.

01059, 00000, "parse expected before a bind or execute"

// *Cause: The client application attempted to bind a variable or execute

// a cursor opened in a PL/SQL block before the statement was parsed.

// *Action: Ensure the statement is parsed before a bind or execute.

01060, 00000, "array binds or executes not allowed"

// *Cause: The client application attempted to bind an array of cursors or

// attempted to repeatedly execute against a PL/SQL block with a bind

// variable of type cursor.

// *Action: Bind a single cursor or execute the PL/SQL block once.

All of the “ORA” error messages are contained in this file. oerr will find the error number in this file utilizing awk commands and display the results back to the terminal.

oerr on Windows

oerr is only available on UNIX , but it does not take much code to access the same message file and display the same messages on a Windows machine. To do this, the actual message file must be ftp’d from a UNIX host to the Windows machine and placed in the same directory as the Java program.

The Java program below (oerr.java) reads the message file and displays the text associated with the error code:

< oerr.java

/**

* Title: oerr

* Description: Oracle oerr utility for Windows

* @author Dave Moore

* @version 1.0

import java.io.*;

public class oerr {

static final String badCommand = "Usage: oerr facility error \n" +

"Facility is identified by the prefix string in the error message.\n" +

"For example, if you get ORA-04030, \"ora\" is the facility and \"04030\"\n" +

"is the error. So you should type \"oerr ora 04030\". \n" +

"\n" +

"If you get LCD-111, type \"oerr lcd 111\", and so on.\n";

public static void main (String args[]) {

if (args.length < 1) {

System.out.println(badCommand);

System.exit(0);

}

String fileName = null;

if (args[0].equalsIgnoreCase("ora"))

fileName = "oraus.msg";

else {

System.out.println("No message file available for that facility");

System.exit(0);

}

try {

FileReader fr = new FileReader( fileName );

BufferedReader br = new BufferedReader(fr);

String lineOfText;

while (!(null==(lineOfText=br.readLine()))) {

if (!lineOfText.startsWith("//")) {

if (lineOfText.startsWith(args[1])) {

System.out.println(lineOfText);

while ((lineOfText=br.readLine()).startsWith("/")) {

System.out.println(lineOfText);

}

} // while

br.close();

fr.close();

}

catch (Exception e) {

System.out.println("Exception occurred: " + e);

}

This program was designed to resemble oerr on UNIX as much as possible. It processes facility names, though the ORA facility is the only one used in this program. Other facilities and their message files could easily be added to the program.

The Java program reads the message file line by line until it encounters the actual error code. While it does take more time to retrieve an error message located at the end of the file, the message is returned within a second, even when processing large message files.

To execute this program would require the following syntax:

C:\oracle9i\bin\java oerr ora 00942

To make it easier to execute the program and make it resemble oerr even more, the oerr.bat file below can be created to insulate the user from typing the “java” keyword on the command line.

< oerr.bat

@echo off

java oerr %1 %2

Executing the bat file above from the DOS prompt, the command will work exactly as it does in UNIX :

C:\oracle9i\bin\oerr ora 00942

00942, 00000, "table or view does not exist"

// *Cause:

// *Action:

The next section will specify a way to deal with the apparent lack of information in the output above.

Creating Custom Message Files

The good thing about oerr is that it is always there to use. Similar to the VI text editor in UNIX , oerr will be on every Oracle UNIX server. The challenge is to augment the supplied Oracle messages with your own, tailored to your specific environment.

Rather than editing the message files provided by Oracle, it is better to create new ones. This way, the message files are insulated from Oracle releases and the contents retained, without losing any new Oracle text. For instance, if the text in the Oracle supplied file was modified, what would happen when the next release of Oracle provides more text on the same error? That new information would be lost, and the changes would need to be made again in the new supplied message files.

A much better option is to create a customized facility and component. That way, any Oracle expertise is retained in the Oracle supplied message files, while customized, specific information is added to the new message files. Then, instead of executing oerr and receiving these weak results:

$ oerr ora 00942

00942, 00000, "table or view does not exist"

// *Cause:

// *Action:

The following oerr command could be used to provide output from the customized message file:

$ oerr mycompany 00942

00942, 00000, "table or view does not exist"

// *Cause: A lot of things can cause this error, however, the

// most frequent one in our shop is trying to access

// a table or view in another users schema. You do

// not have access to that schema and therefore Oracle

// says it doesn’t exist for you to see.

// *Action: Try fully qualifying the table or view name

// (scott.table1). Verify that you have privileges to the

// object you’re trying to access.

Notice the only difference is the new facility “mycompany”, which points to its own message file. A customized message file can be created in three easy steps:

1. Add the facility to the facility.lis file.

2. Create a directory that contains the new message file.

3. Create the actual message file.

oerr Alternatives

The greatest weakness of the oerr utility is the content of its messages. Getting rapid feedback about a particular error seems desirable until text like the following is returned:

$ oerr ora 00942

00942, 00000, "table or view does not exist"

// *Cause:

// *Action:

Getting paged at 2:00 AM is not fun, especially when oerr does not provide a “cause” and “action” for the error. Fortunately, there are two alternatives to oerr that do a better job of analyzing and resolving the problem.

· Oracle TechNet – The TechNet site (http://technet.oracle.com) can be a lifesaver. Full documentation is available online, including error messages. TechNet is available to anyone that registers online.

· Oracle Metalink – This is the online support database (http://metalink.oracle.com) for Oracle. Operated by Oracle Corp. Support Services personnel, this site is likely to have the solution for a problem. A “quick search” capability specifies problem solutions.

The oerr utility should be used for quick identification of problem descriptions and actions. Custom facilities and message files can be created to respond to frequent errors specific to your environment. For detailed problem descriptions and analysis, access either TechNet or Metalink over the Internet.