Returning Status Code From SQL Script To UNIX Shell

This tip was in the May/June 2001 issue of Oracle Magazine (pg 20) (no longer available).  To return a status code of over 0 (which usually indicates no error) up to 255, you can use the "raise_application_error" function, passing -20224-status to it, along with the "whenever sqlerror" directive, to return the code back to the UNIX shell.  In the program below, a status of 55 is returned (such as from a function call or used directly in the script itself) to the shell, which can then be tested in a shell script to continue or terminate the processing in that shell script.  Note that for AIX UNIX, the return code is in the "$?" environment variable.  Other unix versions may use "$status" or "$rc" or some other return code variable.  Also, if you want to see the resulting message ("Bummer"), just remove the ">/dev/null" redirection.

sqlplus -s myuserid/mypswd <<EOF >/dev/null
variable rc number
whenever sqlerror exit sql.sqlcode
begin
     -- Below is really your function call returning a status code
     -- of 55 to the rc (return code) variable, which can range from
     -- 0 (no error) to 255 (only one byte can be returned to shell).
     :rc := 55;
     if (:rc <> 0)
     then
          raise_application_error(-20224-:rc,'Bummer');
     end if;
end;
/
EOF
export x=$?
echo $x

Copyright © 2009 by Maristream.   All information, scripts, forms, and other material
on this web site are freely available to all Banner and Oracle Database Administrators,
Systems Administrators, Programmers, and others that may need it.

The webmaster who maintains this web site may be reached at srea@maristream.org. Visit our other web sites:

Disclaimer:  As with all software, especially where it affects your vital data, make sure that you examine theses scripts and that you understand what they do before you use them to see if they would have any adverse effect on your particular setup or database layout.  Make a full backup of your database in case you have to revert to your original copy of the database before the scripts were run.  Use these scripts at your own risk.  As a condition of using these scripts, you agree to hold harmless both Maristream and Stephen Rea for any problems that they may cause or other situations that may arise from their use, and that neither Maristream nor I will be held liable for those consequences.