6

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

Improve this question
6
  • 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.
    – Colin 't Hart
    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.
    – a_horse_with_no_name
    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.
    – Leigh Riffel
    May 21, 2013 at 12:49

3 Answers 3

Reset to default
1

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.

Improve this answer
1

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.

Improve this answer
0

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.

Improve this answer

Your Answer

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

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