Oracle updating database with scripts - automation

Question

I work in a company where we use a lot of Oracle databases, which are used by our in-house software. The database is updated as the software changes and these updates are put in PL/SQL Scripts. We have clients that are still running older versions of our software and so have 'older' versions of our database.

Now when we upgrade a client's software we now have to and run all scripts that were written since the the version the client has running. Sometimes this is a lot of different scripts and it takes a lot of man hours just to manually go run through them.

I wondered if there is some way we could automate the running of each of these upgrade PL/SQL scripts?

Would it be a feasible option to maybe write a little program that will go execute the scripts on the database?

There is also the issue of errors popping up when the scripts are run, when automating how would one deal with that? Perhaps write to a log file.

Any input on automation of running these scripts would be appreciated?

Answer 1

You didn't specify your operating system, but for Unix I would set up .sh script.

It might look something like this:

$UID='myuid' $PWD='password'

sqlplus $UID/$PWD@SIDNAME @pl_sql_script1.sql

sqlplus $UID/$PWD@SIDNAME @pl_sql_script2.sql

---

If you are calling stored PL/SQL procedures, then you should create a script file (.sql) that has the line: execute owner.stored_procedure

Answer 2

A SQL*Plus script can call other scripts.

For example, a SQL*Plus script like:

@@foo.sql
@@bar.sql
@@baz.sql

Will execute foo.sql, bar.sql, and baz.sql all from the directory where sqlplus was executed from - at least in *nix. On windows, it tends to look in $ORACLE_HOME/bin, but I'm pretty sure that behavior's controllable on both platforms through the SQLPATH environment variable. You can, of course, define a full path using environment variables to execute from, like:

@@$MY_APP_UPGRADE_DIR/foo.sql
@@$MY_APP_UPGRADE_DIR/bar.sql
@@$MY_APP_UPGRADE_DIR/baz.sql

Of course, the flip side of that if the script is built for *nix, it won't work on Windows because of the / v \ issue. But that's not an easy fix, especially on *nix where sed can handle that.

Missed the error comment initially:

SET ECHO ON
SPOOL $MY_APP_UPGRADE_DIR/my_giant_log_file
WHENEVER SQLERROR EXIT SQL.CODE ROLLBACK;

Will drop a log file in the directory specified, and bomb out whenever any SQL error is raised by the upgrade script.