Tags: actual, back, database, exit, mysql, ora-errno, oracle, relate, sql, sqlcode, sqlcodethe, sqlerrm, sqlerror, sqlsqlcodesqlerrm, whenever

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

On Database » Oracle

2,150 words with 5 Comments; publish: Mon, 11 Feb 2008 10:48:00 GMT; (25078.13, « »)

Hi,

Is there any way to pass both the SQL.SQLCODE & SQLERRM to the os back ??

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.oracle.itags.org.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.oracle.itags.org.stdwdev2:/home/etladm/test>

So how can I link ORA-00942 and SQLCODE 174 ?

with thanks in advance

Ranjeesh K R

All Comments

Leave a comment...

  • 5 Comments
    • From the Oracle documentation:

      <quote>

      The range of operating system return codes is also restricted on some operating systems. This limits the portability of EXIT n and EXIT variable between platforms. For example, on UNIX there is only one byte of storage for return codes; therefore, the range for return codes is limited to zero to 255.

      </quote>

      #1; Fri, 22 Feb 2008 16:16:00 GMT
    • Hi,

      U mean to say there is no advantage is returing SQLCODE ?

      Like many people advise use of the following is preferred ?..

      whenever oserror exit 1

      whenever sqlerror exit 2

      Is there n't any way to get more info about the error then ?.

      regards

      #2; Fri, 22 Feb 2008 16:17:00 GMT
    • yes, return code will be bitand(sql.sqlcode, 255)+0 in unix.

      So you would get 0 for a sqlcode of 256.

      I would rather do a WHENEVER SQLERROR EXIT 1, then grep for ORA- in your logfile.

      Regards

      Laurent

      #3; Fri, 22 Feb 2008 16:18:00 GMT
    • something like

      [ $? != 0 ] && grep '^ORA' /home/etladm/test/test.log

      #4; Fri, 22 Feb 2008 16:19:00 GMT
    • thanks laurent
      #5; Fri, 22 Feb 2008 16:20:00 GMT