How change Column Data Type from Number to Varchar 2 with out deleting data

Hi,

I landed into a funny problem. One of my table DEPARTMENTS as dep_id as number, recentny my organization started to use characters for department ids. Could you tell me is there any way i can change the data type to character with out emptying the column.

Thanks in advance

07-09-2003, 05:59 PM

slimdave

This might work ...

Code:

alter table
departments
add (dep_id_chr varchar2(30));

update
departments
set
dep_id_chr=to_char(dep_id,'fm99999999999999');

commit;

alter table
departments
drop (dep_id);

alter table
departments
rename column dep_id_chr to dep_id;

you'd probably have to drop any indexes on dep_id and rebuild them afterwards, and you'd have to revalidate any views, pl/sql etc referencing the table.

01-24-2008, 10:33 AM

smi1955g

...but it's going to be terribly slow in case of a hugely populated table (millions records). It will take hours to finish.

01-24-2008, 05:25 PM

davey23uk

was there a need to bring up up a 4 and a half year old thread just to say that?

01-24-2008, 05:38 PM

smi1955g

nice hit! you'll be surprised but it was a very 1st link in google search.
I think, for now, probably, the best solution is to use create as select constuct, don't you think?