How to change data type of a column having values in Oracle

Problem
Suppose there is a table in an Oracle database.
There comes a requirement that a column which has a NUMBER datatype should now be changed to VARCHAR2.

If the table is empty, then a simple ALTER query will solve the problem.
But if the table has some crucial data which cannot be deleted and you try to execute an ALTER query, you would get something like

This error indicates that you are trying to change the data type of a column which contains some data and Oracle will never allow this operation.

Solution

There are two solutions to this problem :

1. Delete all the data from the table and then execute ALTER query. It will run smoothly.
   This solution is not feasible always as there might be data which could not be afforded to be lost and is only possible on test systems.
2. Solution 2 consists of a combination of following steps :
   • Add a new column to the table.
     This column should have the same data type as the type to which we are trying to change the existing column to.
   • Copy the data from the column whose type we are changing to this new column.
   • Delete the column whose type we were trying to change.
   • Rename the newly added column to the column whose type we were trying to change.
   • From the above steps, it is clear that this solution consists of a series of SQL ALTER queries.

Suppose the table name is codippa which has a column visitorcount of type INT.

Now the type of this column needs to be changed to LONG.
According to the above algorithm, following queries will be required to change the data type of the column :

-- add a new column
ALTER TABLE codippa ADD (visitor_temp LONG);
-- copy data from old column to new one 
UPDATE codippa set visitor_temp = visitorcount;
-- remove old column
ALTER TABLE codippa DROP COLUMN visitorcount; 
-- rename new column to older one
ALTER TABLE codippa RENAME COLUMN visitor_temp TO visitorcount;

Above series of four queries perform the same steps as defined in our algorithm.

Let’s tweak in

1. If you try to change column type between CLOB and VARCHAR2, you will get an error like

   SQL Error: ORA-22859: invalid modification of columns
   22859. 00000 –  “invalid modification of columns”
   *Cause:    An attempt was made to modify an object, REF, VARRAY, nested
   table, or LOB column type.
   *Action:   Create a new column of the desired type and copy the current
   column data to the new type using the appropriate type
   constructor.

   The above solution will work in this case also.

2. It is not mandatory to keep all the keywords in SQL in upper case.
3. New column will always be added to the end of table, that is, it will be the last column of the table.