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
Solved

createing an interactive promotion script of 10g oracle

Posted on 2011-09-06
3
277 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 76

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 76

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
ORA-06504: PL/SQL: Return types of Result Set variables or query do not match 4 165
PL/SQL Display based on value 4 39
use lov values 2 61
Oracle - Query link database loop 8 40
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
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 how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

807 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