JL Computer Consultancy

Interpreting Oracle errors on NT

March 1999


Update Jan 2006.


One of the nice little features of Unix that is missing on other platforms is the oerr (Oracle error) utility that translates an error code into an error message. In unix commands like:

        oerr ora 942
        oerr tns 12512

will give you a description for the error number, and sometimes a cause and action. This page gets a little way to recreting this for Windows NT. The error messages are restricted to ORA errors, so the syntax is slightly different, viz.

        oerr 942
        Error 942 is: ORA-00942: table or view does not exist

The code comes in 3 parts - an SQL script to be run by SYS to create a packaged procedure that does the work of translating an SQL code into an SQL error message; an SQL script that executes to call the package; a DOS script to call SQL*Plus logging on as a restricted user to run the SQL script.

To make all this hang together, the batch command has to be in a directory in your PATH, and the oerr.sql script has to be in a directory in your SQLPATH.

One little feature of this process to bear in mind is the way in which an Oracle ID can be created with extremely limited capabilities - in the example the oerr account can do nothing by connect to the database and execute the oerr package.

Back to Main Index of Topics


Create the package

rem
rem     Script:        c_oerr.sql
rem     Author:        Jonathan Lewis
rem     Dated:         Mar 1999
rem     Purpose:       Quick NT fix to get Oracle error message
rem                    using pseudo-oerr command
rem

create or replace package oerr as
procedure oerr(i_error in number);
end;
/

create or replace package body oerr as

procedure oerr (i_error in number) is
 
begin
        dbms_output.put_line(
               'Error ' || i_error || ' is: ' ||
               sqlerrm(-1 * i_error)
        );
exception
        when others then
               dbms_output.put_line('Error:  number not translated');
end;

end;
/

create user oerr identified by oerr;

grant create session to oerr;
grant execute on oerr to oerr;
create synonym oerr.oerr for sys.oerr;


SQL script to use the package

rem
rem     Script:        oerr.sql
rem     Author:        Jonathan Lewis
rem     Dated:         March 1999
rem     Purpose:       Call oerr function for NT error message
rem

set feedback off
set serveroutput on
execute oerr.oerr(&1);
exit


Batch file to call SQL*Plus and run the oerr script

@echo off
rem
rem     Script:        oerr.cmd
rem     Author:        Jonathan Lewis
rem     Dated:         March 1999
rem     PUrpose:       Q and D NT emulation of unix oerr
rem
 
plus80 -s oerr/oerr @oerr %1


Update Jan 2006.

Recently, someone wrote to the AskTom website because they had managed to damage their database by using Toad whilst (apparently) installing or recompiling the oerr package above.

I can’t imagine what they actually did as I’ve just installed the package on a 9.2.0.6 and 10.2.0.1 database without seeing any signs of anomalous behaviour; however, Tom asked me if I would change this note to point out that this package doesn’t need to be installed in the sys schema to work properly. This is true, any sufficiently privileged account (such as a dba account) that can create procedure and create any synonym can install this package and make it available for other users. The only code change needed is in the create synonym statement that references the owner of the package.

Of course, the whole thing was written a long time ago – so you will also have to change the line that calls SQL*Plus from cmd file. I don’t supposed there are many Windows systems left where plus80 actually starts SQL*Plus running.

One little detail which I obviously overlooked when I first published this note is that you might have a glogin.sql and login.sql script that get called when SQL*Plus starts up (and, in 10g, when you do a simple connect userid/pw); and these may make the output from the cmd file look a little messy.


Back to Main Index of Topics