advice on approach for db upgrade script

From: Jeff Kish <jeff.kish_at_mro.com>
Date: Wed, 16 May 2007 11:57:18 -0400
Message-ID: <b4am43lksgkngruhururqm2tmu1rc1g9n7@4ax.com>

Greetings.

I need to write a script or program (I prefer a pl/sql script) to upgrade a database.

The task needs to:
ensure the user has minimum length required for various table.columns save off old rows in some saved tables
create new data rows (the data making up the primary keys are changing) update all the referring columns to use the rows with the new primary key values
delete the old data rows that have the old primary key values delete the saved tables

warn the user if something is wrong and if so:
- clear out data and delete any temp objects

• rollback any transactions
• exit

i.e. leave things either as they were or as they should be after the upgrade

I'm not sure the best approach.
I've read about pl/sql tables, temporary tables, and global temporary tables.
I'm not sure the best approach here. I'd appreciate any insight or

recommendations. The oracle database will be 9.2 or later.

thanks
Jeff Kish Received on Wed May 16 2007 - 10:57:18 CDT