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_201
prompt
prompt Creating roles Started
prompt ==========================
prompt
@@C:\DataBase\create_roles
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.
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_201
prompt
prompt Creating roles Started
prompt ==========================
prompt
@@C:\DataBase\create_roles
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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