Alter Indexed Column Data Type but keep value

[DugsDMan]

I'm finalizing a HUGE group Oracle project and noticed some of my fellow developers created some of their tables using non-standard data definitions when they created their tables. Since allot of data has been entered in these tables, simply dropping and recreating them is not an option. So, I'm trying to find the easiest way to correct it.

Problem:
One of our control fields, ROW_ID, is defined as VARCHAR2(28), when it should be NUMBER(38).

Solution 1?:
Can I simply issue an alter table statement to change this, but be sure the data is the same? All data in this column is numeric, but I'm assuming no...

Solution 2:
My other plan is to create a script (one each for the 40 tables in 22 seperate DBs, so 880 total) that would:

Drop the index
Insert a ROW_ID_TEMP NUMBER(38) column
Set this column = ROW_ID
Drop the ROW_ID column
Create a new ROW_ID NUMBER(38) column
Set the ROW_ID column = ROW_ID_TEMP
Drop ROW_ID_TEMP
Recreate Index

Before I go through all this effort, does anyone have a better approach?

Thanks!
Doug

 

[SantaMufasa]

Doug,

I have a slightly different (and slightly more efficient) method for you:

ALTER TABLE DUGS ADD row_id_temp NUMBER(38);
UPDATE DUGS SET row_id_temp = row_id, row_id=null;
ALTER TABLE DUGS MODIFY row_id NUMBER(38);
UPDATE DUGS SET row_id = row_id_temp;
ALTER TABLE DUGS DROP COLUMN row_id_temp;

Let us know how that works for you.

Mufasa
(aka Dave of Sandy, Utah, USA @ 03:36 (01Oct04) UTC (aka "GMT" and "Zulu"), 20:36 (30Sep04) Mountain Time)

 

[DugsDMan]

Thanks SM, I actually tested the same thing before I left yesterday. Once I got to looking at it, this row isn't a part of the actual key. We use it as a logical index so we know which record to update when moving data from our test environment to live (in the same way, regardless of table design).

Figured doing it this I wouldn't have to drop and add another column and it also keeps the columns in the same order.

Glad to see you have the same end answer I got. That makes me feel better about it.

Thanks SM!