Re: advice on approach for db upgrade script

On 16 May 2007 09:37:11 -0700, fitzjarrell_at_cox.net wrote:

>On May 16, 10: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
>
>Please define what you mean by 'upgrade a database'.
>
>
>David Fitzjarrell

I have an application suite that runs on off of data found in a schema in a database.
I need to upgrade the schema by applying ddl and dml so it runs with a newer version of code.

I think it is ok in my situation to ask the user to ensure that certain columns are made a certain size before running the 'upgrade' script, so I guess I need to assemble an upgrade script to upgrade the customer data in the schema.

So by 'upgrade a database' is not an 'oracle upgrade', but upgrading some column sizes, and altering the data values in some primary keys in some tables in the customers schema.

thanks
Jeff Kish Received on Wed May 16 2007 - 11:54:14 CDT