When trying to do conversion on fly of a column data type from Number to VARCHAR2, you will receive the error (ORA-01439: column to be modified must be empty to change datatype)

SQL> ALTER TABLE SCHOOL.STAFF_RECORDS

  2  MODIFY(REVIEWED_BY VARCHAR(10));

MODIFY(REVIEWED_BY VARCHAR(10))

       *

ERROR at line 2:

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

 

To resolve this issue:

 

1.      Create a new empty table SCHOOL.STAFF_RECORDS_2 with the “modified/new” data type column being set.

 

CREATE TABLE SCHOOL.STAFF_RECORDS_2

(

  STAFF_NUMBER           NUMBER(15)                   NOT NULL,

  HIRE_DATE    DATE                         NOT NULL,

  REVIEWED_BY      VARCHAR2(10 BYTE)            NOT NULL,

)

 

 

2.       Then, insert records in the new table from the original table:

 

insert  into SCHOOL.STAFF_RECORDS_2  select * from SCHOOL.STAFF_RECORDS;

 

3.       create indexes similar to the original table in the new table.

 

4.       Grant the same permissions on the original table to the new table.

 

5.       Rename the old table with a different name, and the new table with the new one:

 

 

alter table SCHOOL.STAFF_RECORDS  rename to SCHOOL.STAFF_RECORDS_ORIGINAL;

 

alter table SCHOOL.STAFF_RECORDS_2 rename SCHOOL.STAFF_RECORDS;

 

 

Remark: if you face the error (ORA-14047: ALTER TABLE|INDEX RENAME may not be combined with other operation) while re-naming the table, this is because the new table does not need the schema identified to be defined.