Trick to rename a column in a table filled with data in 10G

Alter a table datatype would fail if there is data in it for certain datatypes . Here's a little trick...

Create a backup of the table with the following command.

create table x_backup as (select * from x_details);

Here is the Trick:

1. Add a temporary column with the datatype you want to convert that to as below.

alter table x_details add (errortemp varchar2(100));

2. Update that column with the column you want to change so that the data populates into the new column as below.

update x_details set errortemp=errorcode; commit;

3. Drop the column that we wanted to change the datatype for.

alter table x_details drop column errorcode;

4. Rename the temp column to the column we dropped

alter table x_details rename column errortemp to errorcode;

5. Verify the data as below.

select * from x_error_details;