Re: Is there a table containing ORACLE error codes?

In <1nq7vnINNnp6_at_ef2007.efhd.ford.com> wwm_at_ef5003.efhd.ford.com (Bill Meahan) writes:

>Is there a table in ORACLE (or some other mechanism) by which a user
>program could get the short description of an ORACLE error given the
>error code number?
 

>That is, if I get an error of 1401 (or 01401) is there some way I can
>obtain the short description:
> Inserted value to large for column
>without resorting to the ORACLE Error Messages and Codes Manual?
 

>I'd like to provide something meaningful to my users.
 

>Note: I am NOT using SQL*Forms (and will not convert my client/server
>applications to SQL*Forms).

Bill,

        I assume when you say "user program" that you are using one of the Oracle programmatic interfaces such as Pro*Cobol.

If you are using Pro*cobol, you include the sqlca library. This includes a variable called SQLCODE which is the Oracle error code returned (e.g. 1401) and SQLERRMC which is the text of the error. I would expect all the supported 3gl's to have a similar include file.

If you are using a 3rd party interface program, you would have to ask the distributors of it if they allow the same facility in their software.

Another thing is that Oracle is not very good at telling the host Operating system that an Oracle error occurred. What I did on a VAX/VMS platform was write a small pro*cobol program that had error messages linked into the VMS message facility. I called it "DBS_UP" and its only function was to tell VMS if an Oracle error occurred when a connect request was made to a database. We called it from batch jobs in DCL command procedures before attempting to run the main task of the job. The $status was set at DCL level. THe Command Procedure merely needed to put an "ON ERROR" statement before calling DBS_UP. If success then no probs. If otherwise, the program would set a symbol containing the Oracle error code and also display a DCL error message containing the Oracle error number and text. The procedure would fall through to the specified error label and handle the error as specified by the programmer.

The error handling was usually to VMSMAIL an error message to an Operator and/ or a DBA.

THe program made it easy for us to trap the status of batch jobs and report their failure. e.g...

...

        $ Define/nolog ORA_SID FRED
        $!
	$ On ERROR then goto PROBLEM_WITH_DATABASE
	$ DBS_UP
	$ On ERROR then goto NORMAL_ERROR_HANDLER
	$!
	$ Run A_BATCH_UPDATE
	$!
	$ Exit
	$!
	$ PROBLEM_WITH_DATABASE:
	$!
	$   < DCL text to handle error >

An error message might look like "DBS-F-ORA_1034, Oracle not available."

Hope this helps,
Regards

        Andrew.

-- 
N. Andrew May			
Phone : +61 3 829 5146  	Fax: +61 3 829 6886
Coles/Myer Ltd.			E-mail: andrew_at_cnw01.storesys.coles.oz.au
L1 M11, PO Box 480, Glen Iris 3146, Australia