Tell me more ×
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.
up vote 1 down vote favorite

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
  • 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
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 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 at 20:47
@a_horse_with_no_name: Can't disable substitution vars. See edit. – Martin Apr 17 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 at 8:11

Know someone who can answer? Share a link to this question via email, Google+, Twitter, or Facebook.

Your Answer

 
discard

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

Browse other questions tagged or ask your own question.