column to be modified must be empty to change datatype




An ALTER TABLE MODIFY statement attempted to change the datatype of a column containing data. A column whose datatype is to be altered must contain only NULL values.


To alter the datatype, first set all values in the column to NULL.

Josué Monteiro Viana
2009-02-26 15:24:44

Since you can not simply turn a column into a empty one (normally) I suggest you the following procedure:

1 - Add a temp column in the end of your table:
alter table your_table add (col_temp varchar(300))
2 – Copy the values of your official column to the new one:
update your_table set col_temp = TO_CHAR(your_official_column_name)
3 – Set your official column to null:
update your_table set your_official_column_name = NULL
4 – Adjust your official column with the usual alter table:
Alter table your_table modify your_official_column_name number(X,Y);
OBS: X,Y = Precision scale
5 – Copy the values back to the original column:
update your_table set your_official_column_name = TO_NUMBER(col_temp)
6 – Drop the temporary column:
alter table your_table drop column col_temp

Good lock :)
Josué Monteiro Viana

2010-09-01 11:33:41

This solution worked for me!
2011-08-02 22:14:29
Thanks You explained clearly it worked!

More Information

The Google Search results shown above are obtained by searching for this error using a custom google search engine which is restricted to a specific list of oracle reference sites. If you know a good oracle reference/help site and think that site should be included in this search, you can contribute to this search engine here.