Article copied from http://www.oracle.com/oramag/oracle/01-may/index.html?o31asktom.html

Return Code from SQL*Plus

I have written a UNIX shell script to automate an execution of stored procedures and functions one after another. These pro-cedures/functions return a success/failure code. If one of the procedures fails due to some error, then I want to stop execution of other procedures in the queue and return a failure code in shell script, or otherwise keep continuing and return success code at the end. I don't know how to communicate this return code from Oracle to the UNIX shell script, because this return code becomes unavailable as the control comes back from Oracle to UNIX. This script will be executed by Autosys scheduler, which schedules other jobs as well based on this result.

I assume you are using SQL*Plus. If so, the WHENEVER SQLERROR directive can help. We can return a status from 0...255 to the UNIX shell. This is usually in the $status or $rc environment variable immediately after execution of a command. Here is a CSH script that shows how this might work:

I figured it out... The difference is highlighted in red
 
#!/usr/bin/ksh

sqlplus -s Scott/Tiger <<EOF
 
variable rc number
whenever sqlerror exit sql.sqlcode
 
begin
   :rc := 0;
   :rc := 55 ;
   if (:rc <> 0 ) then
      raise_application_error( (-20000 - 224) - :rc,'Bummer' );
   end if;
end;
/
EOF

x=$?

echo status = $x

When I run this code, I see:

$ test.csh

begin
*
ERROR at line 1:
ORA-20279: Bummer
ORA-06512: at line 5

55  

The 55 is what we're looking for. The key is to use:

raise_application_error( (-20000-224) - :rc, 'Bummer' );

to raise the error. We can raise errors in a given range, but the shell will only keep an unsigned byte in the status return value (values 0...255). It takes our exit file and just looks at that last byte. By using -20000-224 and subtracting from your return code, we end up exiting with the value of your return code (given that :RC is in the range...255).