Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
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 fixed exit; 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)?
You need to change the '&Defvar' to '&&Defvar' and supply a value for Defvar with DEFINE. You can do that from a shell script that feeds into sqlplus and thereafter call the script. For running a script without user interaction you also need to set other settings.
set define offat the start of the script. For more details read the manual.
&nameshould generate an error, as there is no user to enter any value, but
&1should work when a parameter is provided.