Re: WHENEVER SQLERROR EXIT SQL.SQLCODE||SQLERRM & Relate ORA-ERRNO &SQLCODE

From: Daniel Fink <Daniel.Fink_at_Sun.COM>
Date: Wed, 21 Jul 2004 10:00:05 -0600
Message-id: <40FE9305.8E6DC114@sun.com>

SQL*Plus is probably passing 942 out as the return code. However, unix can handle values from 0 to 255. Any return code in excess of 255 is returned as the modulus of (return_code, 256).

SQL> select mod(942,256) from dual;

MOD(942,256)

---

         174

This explains why the $? is 174. If you returned codes 174, 430, 686, 1198, 1454, 1710, 1966, etc. $? would be 174 as well. While this limits the value of passing sql.sqlcode out to the calling script, you could use an exception handler in a pl/sql block to pass out a value that represents likely errors. For example, 0 is success, 1 is unknown failure, 2 is ORA-00942, etc.

Regards,
Daniel Fink

"Ranjeesh K R." wrote:

> Hi,
> Qn 1
> --------
>
> Is there any way to pass both the SQL.SQLCODE & SQLERRM to the os back ??
>
> Qn 2
> -------
> When I tried WHENEVER SQLERROR EXIT SQL.SQLCODE
>
> The actual oracle error code (ORA-00942) and the error code (174 ) returned by SQL.SQLCODE are different . So how can I get the info. that is related to SQL.SQLCODE
>
> eg :
>
> For this test program
> ------------------------
> rm -f /home/etladm/test/test.log
> sqlplus -S <<EOF >> /home/etladm/test/test.log
> ranjeeshk/ics
> set verify on time on timing on term on echo on feedback on serveroutput on
> WHENEVER SQLERROR EXIT SQL.SQLCODE
> -- WHENEVER SQLERROR EXIT SQLERRM
> select sysdate sdate from dua;
> exit 1
> EOF
> echo "Number of rows are: $?" >> /home/etladm/test/test.log
> echo " -------- Log file -------- \n"
> cat /home/etladm/test/test.log
>
> The output was
> -----------------------
> etladm_at_stdwdev2:/home/etladm/test>. ./test.ksh
> -------- Log file --------
>
> select sysdate sdate from dua
> *
> ERROR at line 1:
> ORA-00942: table or view does not exist
>
> Number of rows are: 174
> etladm_at_stdwdev2:/home/etladm/test>
>
> So how can I link ORA-00942 and SQLCODE 174 ?
>
> with thanks in advance
> Ranjeesh K R
>
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request_at_freelists.org
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------

---

Please see the official ORACLE-L FAQ: http://www.orafaq.com

---

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Wed Jul 21 2004 - 11:05:35 CDT