How to Change a Column from Number to Varchar2

This post, tested on Oracle 9, shows how to change a column definition from number to varchar2. First, create a test table:

SQL> col my_letter format a9
SQL> col my_number format 999999999
SQL> create table andrew1
  2  (my_letter varchar2(1),
  3   my_number number(1))
  4  /

Table created.

SQL> desc andrew1
Name                    Null?    Type
----------------------- -------- ----------------
MY_LETTER                        VARCHAR2(1)
MY_NUMBER                        NUMBER(1)

SQL>

Then add three rows of data:

SQL> insert into andrew1 values('A',1)
  2  /

1 row created.

SQL> insert into andrew1 values('B',2)
  2  /

1 row created.

SQL> insert into andrew1 values('C',3)
  2  /

1 row created.

SQL> select * from andrew1
  2  /

MY_LETTER  MY_NUMBER
--------- ----------
A                  1
B                  2
C                  3

SQL>

Next, try to modify the number column. This will fail and display an appropriate error message:

SQL> alter table andrew1 modify(my_number varchar2(1))
  2  /
alter table andrew1 modify(my_number varchar2(1))
                           *
ERROR at line 1:
ORA-01439: column to be modified must be empty to
change datatype

SQL>

Create another table and store the values in the column to be modified along with their rowids: 

SQL> create table andrew2 as
  2  select rowid my_rowid, my_number from andrew1
  3  /

Table created.

SQL>

Now remove the values from the column being modified and try the modify command again. This time it works:

SQL> update andrew1 set my_number = null
  2  /

3 rows updated.

SQL> alter table andrew1 modify(my_number varchar2(1))
  2  /

Table altered.

SQL> desc andrew1
Name                    Null?    Type
----------------------- -------- ----------------
MY_LETTER                        VARCHAR2(1)
MY_NUMBER                        VARCHAR2(1)

SQL>

Finally, using the rowid, reinstate the values in the modified column:

SQL> update andrew1 x
  2  set my_number =
  3  (select my_number from andrew2
  4   where my_rowid = x.rowid)
  5  /

3 rows updated.

SQL> col my_number format a9
SQL> select * from andrew1
  2  /

MY_LETTER MY_NUMBER
--------- ---------
A         1
B         2
C         3

SQL>