Re: Help - clueless Oracle newbie in distress..

In article <46b9dbb9.0111131416.5887ab2f_at_posting.google.com>, sorby_at_my-deja.com (Sorby) wrote:
>Hi,
>
>I'm investigating the side-effects of changing a numeric field
>(integer 4) to alpha-numeric length 5 on Oracle (don't know what
>version, but it's on Unix. I'm just a dumb programmer who never gets
>to see his code running over Oracle).
>
>It's a primary key field so I expect the corresponding index(es) to
>grow. I assume just 2 bytes are currently being used to store the
>numeric field but the new alpha field will require 5 bytes?
>
>When we re-org the database can I expect the contents of the numeric
>field to vapourise or am I expecting too much for the contents to be
>converted to alpha-numeric automatically?
>
>Thanks in anticipation!

You're going to have to create another column first. Oracle won't allow you to change the datatype of the column unless it's empty (ie. no non-null values), which is probably out of the question and non-helpful even if it wasn't. There's also the question of any foreign key constraints that may be dependant on this primary key you're wanting to change. You'll also have to change any foreign key columns to match your new primary key and drop and recreate the foreign key constraints on those dependant tables, which means your DBA will need to do some homework beforehand to determine which those are.

To convert the column without losing data, you'll need to drop the primary key constraint (cascade), create a new (temporary) column in your table, update all the rows to set the new column to the old column's values and set the old column to null, convert the column to the new datatype, update all the rows to set the old column to the new column's values (restoring the data to the converted column), drop the new column, and rebuild the primary key constraint and any foreign key constraints. Again, you'll also have to convert the foreign key columns in the dependant tables as well.

HTH Received on Wed Nov 14 2001 - 08:11:35 CST