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