Re: advice on approach for db upgrade script

On 16 May 2007 09:59:47 -0700, "rogergorden_at_gmail.com" <rogergorden_at_gmail.com> wrote:

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

Well I thought I described the problem ok, but evidently not. I am not sure I have the expertise to figure out how to do this in just sql. I basically want to prepend primary keys in some tables with a certain string.

I researched things and it sounds like doing things in pl/sql and using a varray to hold temporary values etc is a reasonable way to go. You know something like:

pl/sql code to:

step 1 check column lengths currently in tables to make sure they meet minimum requirements - if not tell user to fix them and try later.. exit pl/sql

step 2 check actual data in primary keys to see if there is room for the same records with a longer primary key - if not tell the user tl lengthen columns and try later.. exit pl/sql

store data from each table that has a primary key data change to a varray as a backup
insert into each such table rows with new primary keys.. ignoring duplicates (i.e. the new primary key of one row might equal the exising primary key of another row). if there is an error, roll back, inform the user and exit pl/sql

update all the tables referencing the changed primary key values to use the new values which should exist now. if there is an error, roll back, inform the user and exit pl/sql

finally delete all the rows in the tables with the changed primary key values that have the original primary key values, except of course if any of them are also in use and a modified primary key value.

That way I won't use temp or global temporary tables, and I can query and store results. I assume I can query varrays?

the only thing missing is figuring out how to inform the user something went wrong with enough detail for them to fix things and try again.

thanks
Jeff
Jeff Kish Received on Wed May 16 2007 - 13:13:23 CDT