How to Fix ORA-01439: Column to Be Modified Must Be Empty to Change Datatype in Oracle

ORA-01439 means ALTER TABLE ... MODIFY tried to change a column's data type, for example from NUMBER to VARCHAR2, while the column still holds values. Oracle does not convert existing data in place for such changes. The usual way is to add a new column with the new type, copy and convert the data, drop the old column, and rename the new one.

Reproduce the Error

Example:

create table demo_items (item_id number, code number(6));
insert into demo_items values (1, 123456);
commit;
alter table demo_items modify code varchar2(6);

Output:

Table DEMO_ITEMS created.

1 row inserted.

Commit complete.

Error starting at line : 4
In command -
alter table demo_items modify code varchar2(6)
Error report -
ORA-01439: column to be modified must be empty to change datatype

Fix: Add, Copy, Drop, Rename

Example:

-- add a new column, copy, drop the old one, rename
alter table demo_items add code_txt varchar2(6);
update demo_items set code_txt = to_char(code);
alter table demo_items drop column code;
alter table demo_items rename column code_txt to code;
select * from demo_items;

Output:

Table DEMO_ITEMS altered.

1 row updated.

Table DEMO_ITEMS altered.

Table DEMO_ITEMS altered.

   ITEM_ID CODE
__________ _________
         1 123456

The column keeps its name, now as VARCHAR2(6), with the values converted by TO_CHAR. The column moves to the end of the table's column order, which matters only to SELECT *.

Things to Know

• Changes within the same type, such as a longer VARCHAR2 or a larger NUMBER precision, work on filled columns.
• On large tables, run the copy in batches, or rebuild online with DBMS_REDEFINITION to keep the table available.
• Constraints and indexes on the old column are dropped with it, and triggers that use it become invalid; recreate them for the new column.

Related Guides

• How to Redefine Tables Online with DBMS_REDEFINITION
• How to Fix ORA-01438: Value Larger Than Specified Precision in Oracle

Conclusion

ORA-01439 means a column with data cannot change its data type directly. Add a column of the new type, copy the converted values, drop the old column, rename the new one, and recreate anything that depended on it.