Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

createing an interactive promotion script of 10g oracle

Posted on 2011-09-06
3
Medium Priority
?
283 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 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 77

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
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…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

722 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