SQL*Plus : Force it to return an error code.

Question

Hi,

I have a stored procedure that has an OUT parameter, indicating an error code. If the error code is not 0, then I raise an error

DECLARE
BEGIN
 foo (err_code);

 IF (err_code <> 0) THEN
   raise_application_error(...);

END;

So far so good, but here's my question.

This piece of code (shown above) is executed by sqlplus, which is called from a shell script, which should exit with 0 / not 0 (as the sql script).

#shell script

sqlplus ... @myscript
return $?

When the raise_application_error executes, control goes back to sqlplus.

sql>

What I want, is a way of exiting back to the shell, without sqlplus returning a 0 on $?

Any thoughts? Thanks in advance.

Answer 1

WHENEVER SQLERROR EXIT 1

Answer 2

If you care which application error your PL/SQL raised, you can declare the return code as a SQL*Plus variable, and return have the PL/SQL procedure set it.

#shell script

sqlplus /nolog << EOF
connect uid/pw
variable retval number;
BEGIN
  foo (:retval);
END;
/

exit retval;
EOF

return $?