For an automated task I would very much like to run some SQL scripts and make sure that sqlplus
does not hang under any circumstancees, i.e.:
- If the script contains any named substitution variable that has to be entered by the user, sqlplus should return with an error instead of prompting for the variable -- I cannot use
set define off
, as some of these scripts need to accept command line parameters that have to be resolved with&1
The script must not "hang" when it doesn't contain an
exit;
at the end.Solved: I think now that I can achieve this by wrapping the sql-script in a secondary "caller script". I.e., the caller script calls the other script with
@
and then has a fixedexit;
after that. The other script doesn't need an exit that way.- Anything else: If it would require a prompt, it should return with an error.
How can i do this with Oracle (and sqlplus
or something else)?
set define off
at the start of the script. For more details read the manual.&name
should generate an error, as there is no user to enter any value, but&1
should work when a parameter is provided.