Re: advice on approach for db upgrade script

On May 16, 3:23 pm, Jeff Kish <jeff.k..._at_mro.com> wrote:
> On Wed, 16 May 2007 20:27:13 +0200, Frank van Bortel
>
>

[Please trim what you are not referencing]
>
> 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.

That's why you take a database base backup first. If you are going to have a DBA involved, then why bother with your script? Do it all manually it it is so special case sensitive.

>>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'm really not understanding this length issue. Either the column is large enough to hold the data they have (no size change required OR they are about to add data that is longer than anything previously used, in which case you need to know the Maximum expected length. And you need to know that maximum before your conversion process starts.

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

Think of these extra table as audit logs, storing everything that changed. Take a little time to design them correctly and you can have a undo process that quickly puts everything back in place.

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

As mentioned elsethread, any DDL in Oracle has an implied COMMIT; so if you are altering tables in place, then you cannot mix the DDL and DML and expect ROLLBACK; to save you. Also mentioned elsewhere here is Tom Kyte's advice and mine too: Use SQL (sql*plus) if possible.

HTH,
  Ed Received on Thu May 17 2007 - 11:20:39 CDT