Windows oerr for Oracle

If you have installed this free Windowsoerr program, Perl for Windows, and Oracle Documentation on your computer, you'll be able to type oerr ora 600 or oerr imp 1 or oerr IMP-00001 at the DOS prompt to get a full description of the Oracle error, exactly like what you get on UNIX: 

C:\>oerr ora 1
ORA-00001 unique constraint (string.string) violated
Cause: An UPDATE or INSERT statement attempted to insert a duplicate key.
Action: Either remove the unique restriction or do not insert the key.
C:\>oerr IMP-00001
IMP-00001 respond with either string, string, RETURN or '.' to quit
Cause:    An invalid response was entered.
Action:    Enter any of the responses shown in the message.

The usage that allows "-" and prepended 0's in the error code offers convenience in that you could type oerr followed by a copy and paste of the actual Oracle error from another screen.

There're several approaches to building this program for Windows. For a summary of those approaches, please read the FAQ I wrote and published on Jonathan Lewis's site. Oracle for Windows only has binary message files; their names end with ".msb" and are opened by Oracle processes (threads in Windows). There're no human-readable message files, whose names end with ".msg" as $ORACLE_HOME/rdbms/mesg/oraus.msg on UNIX. So far I've found only one way to retrieve message text from the binary message files given an error number, using oratclsh utility, msgtxt or msgtxt1 function. 

C:\>echo msgtxt rdbms ora 10046 | oratclsh | findstr "ora-10046"
oratclsh[1]- oratclsh[2]- ora-10046: enable SQL statement timing
Unfortunately oratclsh won't work in 10g or is simply not bundled in 10g for Windows, and when it works in pre-10g, you only get the error message text, no cause and suggested action.

My Windowsoerr program fetches information from the error message HTML page in Oracle documentation. The output contains both Cause and Action. Another reason you may want to install Windowsoerr is that UNIX error message files contain some entries not available in the error message reference in documentation, which in turn contains some entries not available in UNIX error message files. Sometimes the entry is available in both places but incomplete in one of them. E.g., oerr pls 123 in UNIX outputs incomplete information compared with the entry in documentation (or equivalently the output of the same command using Windowsoerr). On the other hand, debugging events scattered in the range ORA-10000 to -10999 are completely missing in documentation. My office computer has both Windows DOS window and a UNIX ssh window open. Often times I type the same oerr command in both windows for this reason. But if you just want to quickly find what an event is about from Windows, the above echo msgtxt ... gives you all you need. (Or inside SQL*Plus, type var s varchar2(500), followed by exec :s := sqlerrm(-10046) and print s.)

Current version of the program works with 8.1.6 up to 10gR2. If you use older Oracle documentation, you may have to make minor changes, mostly $fsp and $lsp in the code. Email me for help if needed.

Before you install this program, install Oracle database documentation locally. If you want to save space, you can delete all PDF files. The minimum for oerr to work is to install all the error message pages. You can either delete all other files and directories after installing documentation, or only download the error pages with a program like wget: 

C:\TEMP>wget -r -l 1 https://dpt-info.u-strasbg.fr/doc/oracle/server.102/b14219/toc.htm
(You have to download it from a non-Oracle site to avoid OTN login and redirect problems. You can further save space by rejecting image files with wget.) After download, you can move the server.102 folder from C:\TEMP\dpt-info.u-strasbg.fr\doc\oracle to a more meaningful location.

To install this program, first install Perl for Windows. Then save oerr.pl (rename from oerr_pl.txt to oerr.pl after download) to your computer at, say, D:\oradoc. Modify $dir, $fsp and $lsp in the code as needed. If you use very old docs, also modify $colon. See source code for clear instruction. At any directory included in your %PATH% (find out by the DOS command PATH), say d:\orant\bin or c:\winnt\system32, create a text file named oerr.bat which contains 

@echo off
d:\perl\bin\perl.exe d:\oradoc\oerr.pl %1 %2
assuming your Perl interpreter and the downloaded oerr.pl are in those two directories.

That's all you need to do! If you don't want to install Perl, you can compile the Perl code into oerr.exe using perl2exe. But paths in it are hardcoded.

Postscript

Searching for the detailed description of an Oracle error is becoming much easier nowadays. A web search is often needed even if you're using Oracle on UNIX where oerr is available, because you need to find real world examples of how to deal with a specific error.

A web search needs the exact code though. Searching for "ORA-600" on the web only returns some results and "ORA-00600" returns more. Results from the first search may or may not be in the second search, vice versa. This is because Oracle often prepends 0's to make a 5-digit error code. But you can't count on this. For instance, ORA-000060 is an exception.

If you have a Metalink account, prefixed 0's can all be stripped so you only need to search once.

To my Computer Page