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

  • by

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 in the right date type.

Solutions

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

  1. Empty the column
  2. Empty the table

1. Empty the Column

Backup Data

We add a column to store the original data.

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

Table altered.

SQL> update employees set hire_date_bak = hire_date;

107 rows updated.

Empty Column

Please make sure the column is the one you want to truncate.

SQL> alter table employees modify (hire_date null);

Table altered.

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

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

Table altered.

Restore Data

SQL> update employees set hire_date = hire_date_bak;

107 rows updated.

Add NOT NULL Constraint Back

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 Table

In some situations, if there're many columns should be modified, then you should consider the next solution.

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.

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.

Leave a Reply

Your email address will not be published. Required fields are marked *