Skip to Main Content

Support altering column datatype to any datatype providing a conversion expression

Sebastien A.Feb 6 2018 — edited Feb 6 2018

When altering a column datatype you can often encouter ORA-01439. "column to be modified must be empty to change datatype".

This happens if the database doesn't know how to convert from one datatype to the other.

For instance with the following table T1

create table T1  (

  C1 NUMBER(38)

);

insert into T1 (C1) values (1);

changing C1 to varchar as follow will fail with ORA-01439

alter table T1 modify C1 VARCHAR(100);

It could be very useful to support a syntax where one can provide the conversion expression to use to compute the new column value. This could look something like this:

alter table T1 modify C1 VARCHAR(100) using TO_CHAR(C1);

In this case after the DDL the C1 column would be a VARCHAR(100) containing TO_CHAR of the old C1 value. Ideally the conversion could be any expression like 'PREFIX' || TO_CHAR(C1)

The current workaround is to:

  • create a new column C2 with the varchar datatype
  • copy the data from C1 to C2
  • drop the constraints/indexes relying on C1
  • drop C1
  • rename C2 to C1
  • recreate the constrains/indexes relying on C1
Comments
Post Details
Added on Feb 6 2018
1 comment
455 views