Re: Returning SQL.SQLCODE from SQLPLUS

From: Ricardo Rocha <rrocha_at_mindspring.com>
Date: Sun, 13 Sep 1998 12:43:50 -0400
Message-ID: <6tgsr3$v6r$1@samsara0.mindspring.com>

Hi!

I believe you're getting weird numbers from SQL*Plus termination codes (when using EXIT SQL.SQLCODE) because all Unix shells (including MKS's KSH under NT) map the exit code to a number between 0 and 255.

If the program's exit code is greater than 255, what you get from $? is actually the exit code modulo 256 (exitCode % 256). This is a shell's fundamental limitation...

EXIT SQL.SQLCODE does convert SQLCODE to a positive integer but when this value is passed to the shell it is truncated to a 256 radix. Alas, any ORA- error greater than 255 will be lost.

As you stated, any attempt to check SQLCODE from a subsequent PL/SQL block will implicitly reset it to 0.

If you can map anticipated Oracle error codes to application-defined codes between 0 and 255 there is a way to force SQL*Plus to exit with the proper number. This is (somewhat kludgily) illustrated as follows:

/***************************************************************************

***/
-- This host variable is used to store application specific error codes
VARIABLE applCode NUMBER

• Test the approach... DECLARE dummy NUMBER; BEGIN
  • Initialize applCode to Ok :applCode := 0;
  • This may raise error ORA-00001 if a duplicate key is found INSERT INTO dept(deptno, dname, loc) VALUES(10, 'ACCOUNTING', 'NEW YORK');
  • This raises error ORA-06502 Numeric or value error SELECT 'X' INTO dummy FROM dual; EXCEPTION WHEN OTHERS THEN IF SQLCODE = -1 THEN :applCode := 1; -- Duplicate key ELSIF SQLCODE = -6502 THEN :applCode := 2; -- Conversion error ELSE :applCode := 255; -- Unknown error END IF; /* Attention: re-raising the error condition will cause PL/SQL to rollback host variable :applCode to its previous value!!!

      Note that by not re-raising the error, the PL/SQL procedure will
      appear to complete satisfactorily...

    */

• RAISE; END; . /

/*
  This code implicitly DEFINEs a SQL*Plus "exitCode" variable   from the value stored in ":applCode"
*/
COLUMN exitCode NOPRINT NEW_VALUE exitCode

SELECT :applCode AS exitCode
FROM dual;

• Done: exit with the proper termination code PROMPT Exiting with application error code &exitCode EXIT &exitCode /*************************************************************************** ***/

Hope this helps...

Regards,

Ricardo Rocha
Plenix Inc Received on Sun Sep 13 1998 - 11:43:50 CDT