Capturing Oracle SQL Error Codes

Shell Programming and Scripting
1

My issue pertains to earlier posts like
exit unix script after sqlerror in procedure - dBforums
SQLPLUS Error Capturing | Unix Linux Forums | Shell Programming and Scripting

We are executing PL/SQL blocks from shell scripts using SQLPLUS. The error code has to be captured into a logfile that is sent to an operations team(and hence has to be accurate and complete)
I am getting a MODDED Error Code due to the 2 byte restriction on the return code from SQLPLUS. What is the workaround to get the actual Oracle Error Code?

2

When a process (or child process exits) the resulting status is an 8 bit word. Therefore valid values in a status are 0-255 of signed or unsigned char if you like.

So: You cannot get values like ORA-0412 passed as 412.
You will have to write something to a log file, or insert into an oracle table.

Example: sqlplus does write errors to stderr, so if you redirect from inside a shell script you capture the error.:

sqlplus user/passwd@oracledb <<EOF  2 >> somerrorlog
WHENEVER SQLERROR EXIT SQL.SQLCODE
begin
  SELECT COLUMN_DOES_NOT_EXIST FROM DUAL;
END;
EXIT;
EOF

This will appear in the file somerrorlog

SELECT COLUMN_DOES_NOT_EXIST FROM DUAL;
       *
ERROR at line 2:
ORA-06550: line 2, column 10:
PLS-00201: identifier 'COLUMN_DOES_NOT_EXIST' must be declared
ORA-06550: line 2, column 3:
PL/SQL: SQL Statement ignored
1 Like
3

In most situations I have done as suggested above.
Also after the sql statement I just check for $?
sqlplus does assign $? a 0 on success and non zero on failure( in most cases)
but other oracle client tools like sqlldr are natorious in honoring the exit code.

4

I definitely check for $?. My conundrum was more on the lines of capturing why the error occurred. :slight_smile: