473,573 Members | 2,598 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Executing Multiple Scripts.

RJ
We currently send product releases to our customers, and often have to
include scripts that need to be ran on the Oracle databases (also do
it for SqlServer customers, but we use a different set of scripts for
that). Some of the Sql scripts can be quite long, and so we break
them out to their own script files and call them all from one main
..sql file, using sqlplus to execute it all.
The admin would run the file like:

c:\sqlplus test/test@test1 @run_updates.sq l

run_updates.sql would be a file like:
-------
set heading on
accept spool_file prompt 'Enter the Name and Directory Path of spool
file to use: '
prompt
accept ts_data default 'TS_DATA' prompt 'Enter the TableSpace Name
used for Table Data: '
prompt
accept ts_index default 'TS_INDEX' prompt 'Enter the TableSpace Name
used for Table Indexes: '
prompt

@test1.sql @ts_data @ts_index
@test2.sql @ts_data @ts_index
commit;
-------

test1.sql would be:

-------
create Table SQL_UPDATE (
SQL_FILE varchar2(20),
DATE_EXECUTED DATETIME,
UPDATE_VERSION) tablespace &TS_DATA;

CREATE UNIQUE INDEX SQL_UPDATE001 ON SQL_UPDATE (
SQL_FILE) TABLESPACE &TS_INDEX;
-------
The problem that I have with this is that there is no error trapping,
and no sanity checks to see if a script has already been ran. At any
given release, there may be several dozen scripts, some of them
running against millions of rows, so I'd like to record when a script
has been ran against a specific Instance, and only run the script if
it hasn't been ran yet. This would help the process if an error does
exist, and the admin's need to restart the execution.

What I was thinking of doing was creating a table that I would use to
record what scripts are ran, and when.. call it SQL_UPDATE - fields
for SQL_FILE, DATE_EXECUTED and UPDATE_VERSION. Before I run a Sql
file, I'd like to check against SQL_UPDATE and see if that file name
exists, if it does skip running that file, if not then run it and if
it is successfull insert a row into SQL_UPDATE.
What I'm wondering is if this concept makes sense, and how would I go
about having the code for checking against SQL_UPDATE in the main
execution file? Can I use PL/SQL to do the checks? Ultimately, I'd
like to have 1 procedure that I pass the command to, it does the check
against the SQL_UPDATE table, runs the command if needed, checks for
errors, and updates if successfull.
Sorry for the long winded description, and thanks for any advice you
can offer.

- Rich Werning
Jul 19 '05 #1
2 12727
rj*******@yahoo .com (RJ) wrote in message news:<57******* *************** ***@posting.goo gle.com>...
We currently send product releases to our customers, and often have to
include scripts that need to be ran on the Oracle databases (also do
it for SqlServer customers, but we use a different set of scripts for
that). Some of the Sql scripts can be quite long, and so we break
them out to their own script files and call them all from one main
.sql file, using sqlplus to execute it all.
The admin would run the file like: []
The problem that I have with this is that there is no error trapping,
and no sanity checks to see if a script has already been ran. At any
given release, there may be several dozen scripts, some of them
running against millions of rows, so I'd like to record when a script
has been ran against a specific Instance, and only run the script if
it hasn't been ran yet. This would help the process if an error does
exist, and the admin's need to restart the execution.

What I was thinking of doing was creating a table that I would use to
record what scripts are ran, and when.. call it SQL_UPDATE - fields
for SQL_FILE, DATE_EXECUTED and UPDATE_VERSION. Before I run a Sql
file, I'd like to check against SQL_UPDATE and see if that file name
exists, if it does skip running that file, if not then run it and if
it is successfull insert a row into SQL_UPDATE.
What I'm wondering is if this concept makes sense, and how would I go
about having the code for checking against SQL_UPDATE in the main
execution file? Can I use PL/SQL to do the checks? Ultimately, I'd
like to have 1 procedure that I pass the command to, it does the check
against the SQL_UPDATE table, runs the command if needed, checks for
errors, and updates if successfull.
Sorry for the long winded description, and thanks for any advice you
can offer.

- Rich Werning

There are two issues here:
1. error trapping/logging/reporting
2. auditting (to answer: when was update0427.sql executed?)

Re: 1. error trapping/logging/reporting
you can approach this at multiple levels.
You say you are using simple SQL scripts. Do you include WHEN ERROR
clauses in your SQL scripts? You can trap errors and deal with them
accordingly.
You do also send backout/recovery scripts don't you? ie if the update
script has ANY problem whatever, the backout script should put the
data back to it's initial state, WITHOUT having the DMA do a restore
of the whole database (A restore is a LAST resort option, so you do
run these updates after a clean backup, right??)

PL/SQL is certainly a more precise methos to controlling the error
handling. It can give you enough flexibility to have checkpoints for
continuing an update when an error does happen. The control is more
fine grained, eg instead of in SQL doing:
UPDATE tableA ...<all affected rows>
UPDATE tableB ...<all affected child rows>
where all the updates on tableA must succeed before starting the
updates to tableB, in PLSQL you can link the parent and child in a
cursor loop and:
UPDATE tableA ...<current row>
UPDATE tableB ...<children of current row>
EXCEPTION
WHEN... deal with this row
and now one row failing to update in tableA can be logged and the rest
of the updates to both tableA and tableB can continue.

The PL/SQL approach is obviously stronger. If these updates are fairly
repeatable (same tables, same kinds of data DML changes, as opposed to
table DDL changes), then building a change control package in PL/SQL
is a good solution. (I've done this with previous clients and it is
well received by the DBA especially.)

But even just wrapping your SQL in OS shell scripts can add some
success/failure reporting capability.

Re: 2. auditting (to answer: when was update0427.sql executed?)
Again, many solutions from OS/file system solutions (as simple as
generate log files) to audit tables down to the attribute level. At
the very least, your script should generate an unabiguous GOOD/BAD
report so the person running it can know whether to proceed to their
next task. Summary reports of rows updated, number expected to be
updated(derived from testing), number of failed updates, go/no go
limits can make the person running the scripts more confident. The
audit trail should be able to identfy at least the rows touched and
when it happened.

Usually a combination of log files and DB tables are used. Again,
consider all the possible tools: SQL PL/SQL, shell scripts, and other
languages/tools.
PERL for example is a great language for programming some updates.
With the perl DB interface, you can connect to multiple databases and
work such updates from one program. (as opposed to dumping an update
data file and copying it to another server to run. The time delay may
mean using old data.)
I would suggest avoiding the ONE-DO-ALL procedure in favor of a PL/SQL
package with appropriate functions and procedures. Don't forget to
include an ID() function that just returns the revision of the
package. You WILL need that. It's a lot easier to
SELECT package.id() from dual;
than to dump source from the database and do a diff with your source
files.

So there are lots of ways, so my key suggestion is to keep in mind the
fact there are two issues here.

HTH,
Ed
Jul 19 '05 #2
Hello:
I've found it helpful to use the simple Spooling (File) Mechanism
built into Oracle. It's easy, shows where you errors are and where to
start again.
EX:
SQL>SPOOL report01.txt
SQL>@run_update s.sql
SQL>SPOOL OFF
I mention this because you prompt for a spool file below and it's
never used (or at least the variable isn't used).

Also, you can do almost anything in PL/SQL including error checking,
rollbacks, commits, logging, conditionals, loops, etc. The O'reilly
PL/SQL book is very helpful (the one with the ants on the cover).

I hope this explanation isn't too simple and that it helps.

-Jonathan Ulfeng

rj*******@yahoo .com (RJ) wrote in message news:<57******* *************** ***@posting.goo gle.com>...
We currently send product releases to our customers, and often have to
include scripts that need to be ran on the Oracle databases (also do
it for SqlServer customers, but we use a different set of scripts for
that). Some of the Sql scripts can be quite long, and so we break
them out to their own script files and call them all from one main
.sql file, using sqlplus to execute it all.
The admin would run the file like:

c:\sqlplus test/test@test1 @run_updates.sq l

run_updates.sql would be a file like:
-------
set heading on
accept spool_file prompt 'Enter the Name and Directory Path of spool
file to use: '
prompt
accept ts_data default 'TS_DATA' prompt 'Enter the TableSpace Name
used for Table Data: '
prompt
accept ts_index default 'TS_INDEX' prompt 'Enter the TableSpace Name
used for Table Indexes: '
prompt

@test1.sql @ts_data @ts_index
@test2.sql @ts_data @ts_index
commit;
-------

test1.sql would be:

-------
create Table SQL_UPDATE (
SQL_FILE varchar2(20),
DATE_EXECUTED DATETIME,
UPDATE_VERSION) tablespace &TS_DATA;

CREATE UNIQUE INDEX SQL_UPDATE001 ON SQL_UPDATE (
SQL_FILE) TABLESPACE &TS_INDEX;
-------
The problem that I have with this is that there is no error trapping,
and no sanity checks to see if a script has already been ran. At any
given release, there may be several dozen scripts, some of them
running against millions of rows, so I'd like to record when a script
has been ran against a specific Instance, and only run the script if
it hasn't been ran yet. This would help the process if an error does
exist, and the admin's need to restart the execution.

What I was thinking of doing was creating a table that I would use to
record what scripts are ran, and when.. call it SQL_UPDATE - fields
for SQL_FILE, DATE_EXECUTED and UPDATE_VERSION. Before I run a Sql
file, I'd like to check against SQL_UPDATE and see if that file name
exists, if it does skip running that file, if not then run it and if
it is successfull insert a row into SQL_UPDATE.
What I'm wondering is if this concept makes sense, and how would I go
about having the code for checking against SQL_UPDATE in the main
execution file? Can I use PL/SQL to do the checks? Ultimately, I'd
like to have 1 procedure that I pass the command to, it does the check
against the SQL_UPDATE table, runs the command if needed, checks for
errors, and updates if successfull.
Sorry for the long winded description, and thanks for any advice you
can offer.

- Rich Werning

Jul 19 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
1941
by: Nick Coghlan | last post by:
Anyone playing with the CPython interpreter's new command line switch might have noticed that it only works with top-level modules (i.e. scripts that are directly on sys.path). If the script is inside a package, the invocation will fail with a "Module not found" error. This PEP is aimed at fixing that :) Cheers, Nick.
15
2571
by: Nick Coghlan | last post by:
Python 2.4's -m command line switch only works for modules directly on sys.path. Trying to use it with modules inside packages will fail with a "Module not found" error. This PEP aims to fix that for Python 2.5. Previously, posting of a draft version of the PEP to python-dev and python-list didn't actually generate any responses. I'm not...
6
14545
by: Alex Vilner | last post by:
Hello! We have a set of individual .SQL scripts which we would like to execute against a MS SQL Server 2000. Is there any way to have ISQL utility (or any other means) to execute all of them without having to establish a separate database connection for each script: isql -Ux -Py -Ss -i script1.sql isql -Ux -Py -Ss -i script2.sql isql...
1
2776
by: Phil | last post by:
Hi, I have my create statments for tables, procedures, views, etc in individual Transact-SQL script files (.sql). I wnat to write another script file that executes these scripts in the correct order to create the database. What is the syntax for executing script files from Transact-SQL?
4
12663
by: chris.dunigan | last post by:
I'm looking for an example of how to execute an existing DTS­ package from an ASP (VB)script and would appreciate any and all response. ­I don't even know if it's possible Thanks - Chuck Gatto Dan Guzman Apr 27 2000, 12:00 am show options
0
2047
by: teckguan | last post by:
Hi everyone, I would like to ask how am I going to execute multiple scripts using UNIX command? I have found a command to execute. However, I cannot execute it. The command is db2 -txf <<EOF $SQLSTMTset schema ADMIN; I do think that maybe the author types wrongly. I hope you guys can help. Thanks.
1
1872
by: teckguan | last post by:
Hi, I would like to ask what are the command to execute multiple db2 scripts? I try out the command below but it does not work. db2 -tf <sql filename> & <sql filename> I hope that someone can help me. Thanks. With regards,
3
2095
by: Kasper | last post by:
Hi Group. I have an old VB6 application which loads a number of gui controls from an inifile and for each control the inifile states the name of the vbscript that should be executed once the control is clicked or otherwise triggered. My question is whether it is possible to execute the vb scripts from C#? There are multiple scripts in the...
2
277
by: RJ | last post by:
We currently send product releases to our customers, and often have to include scripts that need to be ran on the Oracle databases (also do it for SqlServer customers, but we use a different set of scripts for that). Some of the Sql scripts can be quite long, and so we break them out to their own script files and call them all from one main...
0
7744
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7664
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
8175
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7732
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
8032
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
1
5553
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5255
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3691
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
0
989
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.