SQL*Plus : Force it to return an error code
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(...);开发者_StackOverflow社区
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.
WHENEVER SQLERROR EXIT 1
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 $?
精彩评论