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

ORA-01439

ORA-01439 means that the data type of a column you want to change is not empty, you have to make it empty first, then do it again. But the question is: how?

Let's see a case that we want to change DATE data type of a column into VARCHAR2.

SQL> conn hr/hr
Connected.
SQL> desc employees;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
...
 HIRE_DATE                                 NOT NULL DATE
...
SQL> alter table employees modify (hire_date varchar2(20));
alter table employees modify (hire_date varchar2(20))
                              *
ERROR at line 1:
ORA-01439: column to be modified must be empty to change datatype

Although we can convert DATE to CHAR by using TO_CHAR at run-time, but sometimes, we want the column to be stored as character-based.

Solutions

To solve ORA-01439, we may choose one of the following ways:

1. Empty the column
2. Empty the whole table

1. Empty the Column

Backup Data

Here we add a new column with our expected definition VARCHAR2.

SQL> alter table employees add (hire_date_bak varchar2(20));

Table altered.

We copied values of the old column to the new column to make the data backed up.

SQL> update employees set hire_date_bak = hire_date;

107 rows updated.

Meanwhile, DATE was converting into CHAR implicitly.

Empty Column

Please make sure the column is the one you want to empty, then we remove NOT NULL constraint and clear the fields from the column.

SQL> alter table employees modify (hire_date null);

Table altered.

We set it as NULL to empty the column.

SQL> update employees set hire_date = null;

107 rows updated.

As you can see, we make the column nullable before we null it out.

Change Data Type

This is what we want to do in this post, let's change datatype.

SQL> alter table employees modify (hire_date varchar2(20));

Table altered.

Restore Data

Get data back from the new column.

SQL> update employees set hire_date = hire_date_bak;

107 rows updated.

Add NOT NULL Constraint Back

We add NOT NULL back to the column which is used to be NOT NULL.

SQL> alter table employees modify (hire_date not null);

Table altered.

We can verify the result now.

SQL> desc employees;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
...
 HIRE_DATE                                 NOT NULL VARCHAR2(20)
...

You can optionally drop the backup column now.

2. Empty the Whole Table

In some situations, there're several columns should be modified, then you can achieve the goal once for all.

Backup Data

We create a temporary table to store the original data.

SQL> create table employees_bak as select * from employees;

Table created.

Empty Table

Please make sure the target table is the one you want to truncate.

SQL> truncate table employees;

Table truncated.

You may see ORA-02266 in the above TRUNCATE TABLE statement.

Change Data Type

SQL> alter table employees modify (hire_Date varchar2(20));

Table altered.

And then do the rest of columns that need to be modified.

Restore Data

SQL> insert into employees select * from employees_bak;

107 rows created.

We can verify the result now.

SQL> desc employees;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
...
 HIRE_DATE                                 NOT NULL VARCHAR2(20)
...

We did it. No more ORA-01439.

Please note that, we don't have to care about NOT NULL constraints, because truncating a table leaves no footprint.