Re: advice on approach for db upgrade script

On May 16, 11:57 am, Jeff Kish <jeff.k..._at_mro.com> wrote:
> 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

To paraphrase Tom Kyte:

If you can do it in SQL*PLUS, do it.
If you can't do it in SQL*PLUS do it in PL/SQL. If you can't do it in Pl/SQL do it in PERL. (Tom prefers Java, although he admits little or no experience with it.) If you can't do it in PERL do it in C+.

Not sure about all of those requirements. In any case, take a good, restorable backup right before you do anything, then you will be able to go back to before you started.

Roger Gorden Received on Wed May 16 2007 - 11:59:47 CDT