SQLPLUS Error Capturing

Shell Programming and Scripting
1

This is my .sh file

sqlplus -s my_user/my_pswd@mydb  @my_sql.sql

ret_code=$?

if [ $ret_code -ne 0 ]
then

echo "return code is $ret_code "
echo "Failed"
else
echo "return code is $ret_code "
echo "Success"
fi

=====================================================

and my_sql.sql is

whenever SQLERROR exit SQL.SQLCODE;

select 1/0 from dual;

quit;

=======================================================

WHen I run this, this is the output I'm getting,

select 1/0 from dual
        *
ERROR at line 1:
ORA-01476: divisor is equal to zero
 
 
return code  is 196
Failed.

==================================
My question is, why the error code is 196 instead of 1476 ?

2

error code 196 is from the unix and not from oracle.
when you do $? you are getting the last executed unix command status.

3

Ok, I hope the last executed, before I echo the return code,unix command is sqlplus.

What does it imply if the return code of sqlplus command is 196? ..
is there a way I can print -1476 to print the actual error?

Thanks,
Safath.

4

because 196 is the lower eight bits of the number 1476 (integer);

number returned in $? can be 0 - 255 - the lowest 8 bits of a 4 byte number.

5

I'm sorry, How to calculate the lowest 8 bits of any given number? Any pointers on this? Thanks in advance....