How to change datatype of column with data

Let’s say I have a table call_log that has a column named duration. Let’s further pretend that when I put the table together, I made the mistake of making duration a varchar column instead of a number. Now I can’t properly sort on that column. I want to rename the column so I issue…

ALTER TABLE call_log MODIFY (duration NUMBER);

But I get…

ORA-01439: column to be modified must be empty to change datatype.

My table has been in use and has data! And I don’t want to lose the data. How do I modify the column’s datatype without losing data?

Answer

Create a temporary column with the correct data type, copy the data to the new column, drop the old column, rename the new column to match what the old column was named.

ALTER TABLE call_log ADD (duration_temp NUMBER);
UPDATE call_log SET duration_temp = duration;
ALTER TABLE call_log DROP COLUMN duration;
ALTER TABLE call_log RENAME COLUMN duration_temp TO duration;

The idea for this answer came from Oracle’s forums.