Re: advice on approach for db upgrade script

On Wed, 16 May 2007 20:27:13 +0200, Frank van Bortel <frank.van.bortel_at_gmail.com> wrote:

>-----BEGIN PGP SIGNED MESSAGE-----
>Hash: SHA1
>
>Jeff Kish schreef:
>
>> 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
>
>No - just widen the column, and carry on
>>
>> 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
>
>Cannot be a problem - you already fixed that in step 1
>>
>> 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
>
>I'd rather have a table, holding the old and new values.
>Allows for undo, and it seems you have other (referenced tables)
>you need to update, too.
>Use the cuurent value found in the referenced table,
>lookup the new value, using the old, and Bob's your uncle!
>
>>
>> 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
>Why would there be an error?
>>
>> 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.
>>
>I'd wait with the drop table, until everything is working OK.
>Of course you can still restore the backup, but after two
>or three business days, no one will love you for that - they'll
>love you whan you fire off a repair scripts that undoes the
>changes on the fly!
>>
>> That way I won't use temp or global temporary tables, and I can query and
>> store results. I assume I can query varrays?
>>
>Nope - data is lost when ending the session
>> 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

Thanks for the reply...
regarding:
>No - just widen the column, and carry on
you mean alter table command in the procedure? The reason I didn't want to do that was:
1 - if i do it i need to manually find and alter all the referring columns. And if for some reason there is a failure I need to handle that (sounds complicated). But if a prerequisite is that the dba uses, say, enterprise manager to length the primary key and related columns, then they can do that better themselves in one fell swoop.
>Cannot be a problem - you already fixed that in step 1
no, there is a minimum length column we want the customers to use for this version. However it is always possible that this minimum length isn't enough for the data they use so I'd need to either tell them to lengthen it or lengthen it ourself.
>I'd rather have a table, holding the old and new values.
>Allows for undo, and it seems you have other (referenced tables)
>you need to update, too.
>Use the cuurent value found in the referenced table,
>lookup the new value, using the old, and Bob's your uncle!
I keep hearing warnings about using temp tables etc. I thought I should stay away from them. If I use these don't I need to create them before the procedure is run, otherwise it won't see them at compile time will it? Just trying to make sure I take a course here with more wisdom than I inherently have.
>> That way I won't use temp or global temporary tables, and I can query and
>> store results. I assume I can query varrays?
>>
>Nope - data is lost when ending the session
I was hoping that if everything ran ok, I wouldn't care about the old data, and if everything was not ok I would not commit the transaction(s).

thanks for your patience and advice.
Jeff Kish Received on Wed May 16 2007 - 14:23:33 CDT