How to return an exit code from SQL*Plus to the Unix Shell ?[akadia]

If you want to return a code from SQL*Plus to the UNIX shell script, then the WHENEVER SQLERROR directive can help. We can return a status from 0...255 to the UNIX shell. This is usually in the $? environment variable ( for the Korn or Bash Shell ) immediately after execution of a command. Here is a KSH script that shows how this might work:

#!/bin/ksh

sqlplus -s scott/tiger </dev/null 2>&1

variable rc number
whenever sqlerror exit sql.sqlcode

begin
-- The Variable rc is the return code (from your programm logic)
-- which you want to send from SQL*Plus to the shell, when
-- the scripts aborts due the WHENEVER SQLERROR
:rc := 1;
if (:rc <> 0)
then
raise_application_error((-20000-224) - :rc, 'Error!');
end if;
end;
/
EOF

Run this code as follows and you will see:

$ ./test.ksh
$ echo $?

1

The 1 is what you are looking for. The key is to use ...

raise_application_error( (-20000-224) - :rc, 'Error!' );

... 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 ! ).

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/936/viewspace-60613/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/936/viewspace-60613/