createing an interactive promotion script of 10g oracle

I am trying to create a script that would be interactive that would be used to pormote objects from one data base to another. The  o/s is Windows but I wanted to run the script from a sqlplus window.

The idea would be as each part of your promotion script completed you would be promted to continue, if you say no you exit the script


// Main script
spool C:\logs\PROMOTION_TEST_20110906.log

prompt
prompt Creating roles Started
prompt ==============================
prompt
@@C:\DataBase\create_roles.sql
prompt
prompt Creating roles ended
prompt ==============================
prompt

spool off


-- create_roles.sql script is

-- Create the role
create role TEST_ROLE1;
create role TEST_ROLE2;


in this example I would want the main script to pause after each statement in the create_roles script. For example if there was an error like ORA-01921 after trying to create TEST_ROLE1 I could choose to ignore the failure and continue on, but if there was some other error I could choose to exit the promotion.

I would idealy like to be able to default to continue and only ask the question if there was an error. or have an over-ride that would just continue what ever the error.

This is all part of trying to establish a regular process to promote changes to the data-base.
LVL 2
Jayesh AcharyaTechnichal ConsultantAsked:
Who is Participating?
 
slightwv (䄆 Netminder) Commented:
Not sure how you can do this using native SQLPLUS.

You can exit the entire script whenever a 'failure' occurs with simple WHENEVER syntax:

http://download.oracle.com/docs/cd/B19306_01/server.102/b14357/ch12052.htm

To get the 'ignore/continue/exit' functionality I believe you will need an actual programming language.


You can simulate it with an accept call:
accept junk_var prompt 'Ctrl-C to quit, <enter> to continue'
0
 
Jayesh AcharyaTechnichal ConsultantAuthor Commented:
if I have a dos prompt

do you know how I could make this script interactive from msdos?

I can always run a sqlplus command from dos, but I am pretty weak at trying to get the DOS window to simulate the part of accept junk_var prompt

I am assuming that after each call in my create role script I would need to have the WHENEVER SQLERROR EXIT SQL.SQLCODE


-- create_roles.sql script is

-- Create the role
create role TEST_ROLE1;
WHENEVER SQLERROR EXIT SQL.SQLCODE;


create role TEST_ROLE2;
WHENEVER SQLERROR EXIT SQL.SQLCODE;





so the script would look something like
0
 
slightwv (䄆 Netminder) Commented:
I believe WHENEVER is set once per session/connection.

>> get the DOS window to simulate the part of accept junk_var prompt

If accept will work why do you need to simulate it?

>>do you know how I could make this script interactive from msdos?

Sorry but my BAT scripting is pretty weak as well.  I would Google around myself.

Powershell or VBScript might be a better option.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.