Take the 2-minute tour ×
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's 100% free, no registration required.

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

share|improve this question
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 '13 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 '13 at 20:47
    
@a_horse_with_no_name: Can't disable substitution vars. See edit. –  Martin Apr 17 '13 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 '13 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 '13 at 12:49

2 Answers 2

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.

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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