Solved

createing an interactive promotion script of 10g oracle

Posted on 2011-09-06
3
278 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 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 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 500 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

763 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