Changing Data Type of Non Empty Column In Oracle

When you change the data type of non-empty column you will get an error. To overcome this you can make a trick as shown below:


Connected to Oracle Database 10g Enterprise Edition Release 10.1.0.2.0
Connected as sysadm

SQL>
SQL> drop table z;

Table dropped

SQL> create table z( i number);

Table created

SQL> INSERT INTO z VALUES( 4);

1 row inserted

SQL> commit;

Commit complete

SQL> alter table z modify i varchar2(12);

alter table z modify i varchar2(12)

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

SQL> desc z;
Name Type Nullable Default Comments
---- ------ -------- ------- --------
I NUMBER Y

SQL> alter table z add(ii number);

Table altered

SQL> update z set ii = i;

1 row updated

SQL> SELECT * FROM z;

I II
---------- ----------
4 4

SQL> update z set i = null;

1 row updated

SQL> alter table z modify i varchar2(12);

Table altered

SQL> update z set i = ii;

1 row updated

SQL> alter table z drop column ii;

Table altered

SQL> desc z;
Name Type Nullable Default Comments
---- ------------ -------- ------- --------
I VARCHAR2(12) Y

SQL> drop table z;

Table dropped

SQL>