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)?

  • 1
    I think you can do this under Linux/Unix when you embed the SQL commands in a shell script and pipe them into a SQL*Plus instance. Apr 16, 2013 at 19:36
  • 1
    for the first item, simply add an set define off at the start of the script. For more details read the manual.
    – user1822
    Apr 16, 2013 at 20:47
  • @a_horse_with_no_name: Can't disable substitution vars. See edit.
    – Martin
    Apr 17, 2013 at 7:40
  • @a_horse_with_no_name: I meant to say &name should generate an error, as there is no user to enter any value, but &1 should work when a parameter is provided.
    – Martin
    Apr 17, 2013 at 8:11
  • Why do you want the script to run without user interaction? Is it so that another script after it will also run or so an OS process will run when the script completes? Knowing your goal will help devise the best plan. May 21, 2013 at 12:49

3 Answers 3


You could execute each script using a separate call to SQLPlus and have them run in the background.

If you just need to remove errant parameters you could search for them in the scripts and not run those scripts that contain un-supplied parameters.


You can do this under Linux/Unix when you embed the SQL commands in a shell script and pipe them into a SQL*Plus instance.


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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.