RE: WHENEVER SQLERROR EXIT SQL.SQLCODE||SQLERRM & Relate ORA-ERRN O & SQLCODE

From: Kevin Lange <klange_at_ppoone.com>
Date: Wed, 21 Jul 2004 10:05:39 -0500
Message-ID: <ED1256BD4F253C44B1627B2D365A334F0528FB62@ppoone1.ppoone.com>

As Daniel pointed out yesterday, the Exit line can only accept up to 256. After that it uses the Mod of the number and 256. 942 Mod 256 is 174. Hence, the reason you are getting 174 back.

If you really need the value of the error message and the exact sqlcode you will have to use a different method .

Maybe a hybrid between using the exit code and writing the actual error message out to a file.

-----Original Message-----
From: Ranjeesh K R. [mailto:ranjeeshk_at_infics.com] Sent: Wednesday, July 21, 2004 9:35 AM
Subject: WHENEVER SQLERROR EXIT SQL.SQLCODE||SQLERRM & Relate ORA-ERRNO & SQLCODE 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 - 10:31:29 CDT