Windows oerr for Oracle

[Update 2013-08] Oracle 12c for Windows has finally shipped Windows oerr, making my 13-year-old program obsolete. To use it, you need to set s_oraclehome to the same as %oracle_home%, set perl5lib to %s_oraclehome%\perl and if 12.1.0.1, copy at least oraus.msg from any Linux/UNIX Oracle box, which is missing in %oracle_home%\rdbms\mesg (or use Dennis Yurichev's msb files unpacker to unpack oraus.msb): 
D:\app\oracle\product\12.1.0\db\RDBMS\mesg>set s_oraclehome=D:\app\oracle\product\12.1.0\db

D:\app\oracle\product\12.1.0\db\RDBMS\mesg>set perl5lib=D:\app\oracle\product\12.1.0\db\perl

D:\app\oracle\product\12.1.0\db\RDBMS\mesg>pscp oracle@mylinuxbox:/u01/app/oracle/product/12.1.0/db/rdbms/mesg/oraus.msg .
oracle@mylinuxbox's password:
oraus.msg                 | 5331 kB | 2665.7 kB/s | ETA: 00:00:00 | 100%

D:\app\oracle\product\12.1.0\db\RDBMS\mesg>oerr ora 1
00001, 00000, "unique constraint (%s.%s) violated"
// *Cause: An UPDATE or INSERT statement attempted to insert a duplicate key.
//         For Trusted Oracle configured in DBMS MAC mode, you may see
//         this message if a duplicate entry exists at a different level.
// *Action: Either remove the unique restriction or do not insert the key.


If you have installed this free Windowsoerr program, Oracle database documentation, and either Oracle software or standalone Perl interpreter for Windows on your PC, you'll be able to type oerr ora 600 or oerr imp 1 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 1
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.
C:\>oerr PLS-00103
PLS-00103: string
Cause: This error message is from the parser. It found a token (language element
...

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

There're several approaches to building this program for Windows. (See summary I wrote). 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 as on UNIX whose names end with ".msg", such as $ORACLE_HOME/rdbms/mesg/oraus.msg. Dennis Yurichev's Oracle .msb files unpacker can directly read a .msb file. Before 10g, you can also use oratclsh to read. 

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. In addition, when you read an .msb file, you only get the error message text, no cause or suggested action.

My Windowsoerr program fetches information from the error message HTML pages 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. For instance, 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 mostly 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 Oracle on Windows, inside SQL*Plus, type var s varchar2(500), followed by exec :s := sqlerrm(-10046) and print s.)

Current version of the Windowsoerr program works with 8.1.6 up to 11gR2. (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 http://download.oracle.com/docs/cd/B19306_01/server.102/b14219/toc.htm
If you want, you can further save space by rejecting image files with wget. After download, you can move the server.102 folder from C:\TEMP\download.oracle.com\... to a more meaningful location.

To install this program, make sure you have Perl interpreter installed. If you have Oracle client or server on the PC, the path for perl.exe is something like %ORACLE_HOME%\perl\5.8.3\bin\MSWin32-x86-multi-thread. If you don't have Perl, you can install ActiveState 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), create a text file named oerr.bat which contains 

@echo off
c:\oracle\product\10.2.0\client_1\perl\5.8.3\bin\MSWin32-x86-multi-thread\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 may want 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 number of digits to be five. For instance, ORA-000060 in slightly older version is an exception.

If you search on MyOracleSupport, the prefixed 0's can be stripped because the search engine is Oracle-aware. So you only need to search once.



To my Computer Page