ORA-01439: to change the data type, the column to be modified must be empty

This method has three update operations. We recommend that you test and evaluate the efficiency based on the actual data volume.

Idea: Define the column [col_old] for the data type to be updated, the data type is [datatype_old], the temporary column [col_temp], and the data type is also [datatype_old].
Add [col_temp] to the table according to [col_old], assign the data of [col_old] to [col_temp], and then clear the data of [col_old, modify the Data Type of [col_old] to [datatype_new], assign the data of [col_temp] to [col_old], and delete [col_temp].

The following example shows a general reference script to change the data type of a column in a table from varchar2 (64) to number.

1. Define variables and assign values

Define table_name = table name define col_temp = column name _ tempdefine col_old = column name define datatype_old = varchar2 (64) define datatype_new = Number

2. Execute the script

prompt 1.alter table &table_name add &col_temp &datatype_old;alter table &table_name add &col_temp &datatype_old; prompt 2.update &table_name set &col_temp = &col_old;        update &table_name set &col_temp = &col_old;        commit; prompt 3.update &table_name set &col_old = null;        update &table_name set &col_old = null;        commit; prompt 4.alter table &table_name modify &col_old &datatype_new;alter table &table_name modify &col_old &datatype_new; prompt 5.update &table_name set &col_old = &col_temp;update &table_name set &col_old = &col_temp;commit; prompt 6.alter table &table_name drop column &col_temp;alter table &table_name drop column &col_temp;

 

ORA-01439: to change the data type, the column to be modified must be empty