Re: advice on approach for db upgrade script

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

• -- Regards, Frank van Bortel

Top-posting is one way to shut me up...
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (MingW32)

iD8DBQFGS00BLw8L4IAs830RAh4gAJwLrGv1VwkyLfdqSymR2xws9YFqhgCgiHvV 8MDUjvBK7umf3YDZu1X0v70=
=H9Tr
-----END PGP SIGNATURE----- Received on Wed May 16 2007 - 13:27:13 CDT