ORA-01439 When trying to change CHAR to VARCHAR2

I recently had a coworker ask me about a strange situation: They were trying to modify a column on a table to go from a CHAR(4 BYTE) to a VARCHAR2(5 BYTE). Oracle, however, didn’t seem to want to cooperate.

Oracle returned an ORA-01439: column to be modified must be empty to change data type exception.

Here I’ve drummed up a quick example of what was going on:

I knew, however, that this should be possible for what we were trying to do. As it turns out, the solution was easy, but not obvious. There was a check constraint to ensure that the column was a numeric value.

Below is the full code leading up to the alter table in my example above:

SQL> create table kaleyc.drop_me
  2  (
  3     x char(4) constraint ck_numbers check (decode(translate(x, 'A1234567890', 'A'), null, 1, 0) = 1)
  4  );
 
Table KALEYC.DROP_ME created.
 
SQL>
SQL> insert into kaleyc.drop_me
  2  values ('0001');
 
1 row inserted.
 
SQL>
SQL> commit;
 
Commit complete.
 
SQL>
SQL> alter table kaleyc.drop_me modify x varchar2(5);
 
Error starting at line : 25 in command -
alter table kaleyc.drop_me modify x varchar2(5)
Error report -
ORA-01439: column to be modified must be empty to change datatype
01439. 00000 -  "column to be modified must be empty to change datatype"
*Cause:   
*Action:

The solution was to drop the check constraint, modify the column, and re-apply the check constraint.

Disabling and re-enabling the constraint didn’t work–the constraint had to be totally dropped.

SQL> alter table kaleyc.drop_me drop constraint ck_numbers ;
 
Table KALEYC.DROP_ME altered.
 
SQL>
SQL> alter table kaleyc.drop_me modify x varchar2(5);
 
Table KALEYC.DROP_ME altered.

SQL> alter table kaleyc.drop_me modify x constraint ck_numbers check (decode(translate(x, 'A1234567890', 'A'), null, 1, 0) = 1);
 
Table KALEYC.DROP_ME altered.

Free Oracle SQL Tuning Guide

Checkout my FREE guide, 7 SQL Tuning Secrets You Can Use Immediately, Even If You’ve Never Tuned a Query In Your Life!

Get it here: tuningsql.com/secrets