I faced wiht that error message while i was trying to run ALTER TABLE MODIFY statement

My database version is 11.2.0.1, I tried to run ALTER TABLE MODIFY statement on my table than I hit this error message.

 Let me try to explain cause of this error and solution steps.

1. Create demo table:

CREATE TABLE cities
( city_id number(10), 
 city_name varchar2(150)  );

2.  Insert data into table:

INSERT into cities
(city_id , city_name)
values (34, ‘ISTANBUL’);

3. Modify table:

ALTER TABLE cities
 MODIFY city_id varchar2(15);

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

You can only modify the datatype of a column whose values are all NULL.

Solution:

UPDATE cities
SET city_id = NULL;
or
DELETE FROM cities
or
Truncate table cities

Now you can run your alter command.

Advertisements