sqlplus Setting the exit code of a script

Question

How can I set the exit code of a script?

The following doesn't seem to work

WHENEVER SQLERROR EXIT 5
DECLARE
 retCode number := 0;
BEGIN
 retCode := 30; 
END;
/
EXIT :retCode ;

Gives the usage for EXIT with an exit code of 1

Answer 1

Try it like this:

var retCode number
exec :retCode := 30;
exit :retCode

(See http://www.orafaq.com/forum/mv/msg/80574/233106/0/#msg_233106)

Bottom line is that retCode must be a variable defined in SQL*PLUS's scope. Your DECLARE is inside a code block, and SQL*PLUS can't see into it.