Executing Multiple Scripts.
Date: 30 Apr 2004 11:14:14 -0700
Message-ID: <570dd773.0404301014.738703a_at_posting.google.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_at_test1 @run_updates.sql
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
_at_test1.sql @ts_data @ts_index
_at_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