Re: Executing Multiple Scripts.

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>_at_run_updates.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

rjwerning_at_yahoo.com (RJ) wrote in message news:<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
Received on Wed May 12 2004 - 16:58:18 CEST