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 (See ORA-00942.ora-code.com), 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 (See ORA-00942.ora-code.com)) 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@(protected):/home/etladm/test >. ./test.ksh
> -- ----- Log file -- -----
>
> select sysdate sdate from dua
> *
> ERROR at line 1:
> ORA-00942 (See ORA-00942.ora-code.com): table or view does not exist
>
> Number of rows are: 174
> etladm@(protected):/home/etladm/test >
>
> So how can I link ORA-00942 (See ORA-00942.ora-code.com) 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@(protected)
> 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@(protected)
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
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --
