Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

createing an interactive promotion script of 10g oracle

Posted on 2011-09-06
3
Medium Priority
?
284 Views
Last Modified: 2012-05-12
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.
0
Comment
Question by:jhacharya
  • 2
3 Comments
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 total points
ID: 36491534
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
 
LVL 2

Author Comment

by:jhacharya
ID: 36491760
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
 
LVL 78

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 2000 total points
ID: 36492085
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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

783 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question