Sometimes, when working with Oracle databases, you may encounter the need to change a column's data type — for example, converting a numeric column to a string type (`VARCHAR2`). However, unlike some other databases, Oracle does not allow you to directly modify a column's data type if the conversion isn’t straightforward, such as changing a `NUMBER` column to `VARCHAR2`.
So, how do you safely perform this conversion?
In this blog post, I'll walk you through a simple, reliable method to change a column's data type from `NUMBER` to `VARCHAR2` in Oracle.
Why Can’t You Just Use ALTER COLUMN?
Oracle’s `ALTER TABLE ... MODIFY` command can only change a column’s data type if the new type is compatible with the old type — for example, increasing the size of a VARCHAR column. But changing a column from `NUMBER` to `VARCHAR2` is **not compatible** because these data types fundamentally represent different kinds of data.
Trying to do this directly will result in an error.
Steps to Convert NUMBER to VARCHAR2
The recommended approach is to,
- Add a new column with the desired data type (`VARCHAR2`).
- Copy the data from the old `NUMBER` column into the new column, converting the numbers to strings.
- Drop the old column.
- Rename the new column to the original column's name.
- Recreate any indexes or constraints if necessary.
Example Scenario
Assume you have a table called `STOCKHOLDING_SELLDETAILS_CG` and a column called `SCRIPCODE` of type `NUMBER`. You want to convert `SCRIPCODE` to a `VARCHAR2` type.
Step 1. Add a new VARCHAR2 column.
Add a new column with a suitable size (for example, 50 characters).
ALTER TABLE STOCKHOLDING_SELLDETAILS_CG
ADD (
SCRIPCODE_VARCHAR2 VARCHAR2(50)
);
Step 2. Copy and convert data to the new column.
Update the new column with the string representation of the old `NUMBER` data.
UPDATE STOCKHOLDING_SELLDETAILS_CG
SET SCRIPCODE_VARCHAR2 = TO_CHAR(SCRIPCODE);
Then commit the changes.
COMMIT;
Check update
SELECT SCRIPCODE, SCRIPCODE_VARCHAR2 FROM STOCKHOLDING_SELLDETAILS_CGWHERE ROWNUM <= 10;
Step 3. Drop the old column.
ALTER TABLE STOCKHOLDING_SELLDETAILS_CG
DROP COLUMN SCRIPCODE;
Note. If there are constraints or indexes on `SCRIPCODE`, you need to drop or disable them before this step.
Step 4. Rename the new column to the original name.
ALTER TABLE STOCKHOLDING_SELLDETAILS_CG
RENAME COLUMN SCRIPCODE_VARCHAR2 TO SCRIPCODE;
Step 5. Recreate indexes or constraints (if applicable).
If your old column had any indexes or constraints, recreate them on the new column.
Important Notes
- Backup your data before performing these operations on a production system.
- Carefully check for indexes, foreign keys, or constraints on the original column; these need special handling.
- Adjust the size of the `VARCHAR2` column to fit your actual data length.
- Test these changes in a development environment before applying them to production.
Although Oracle does not allow a direct data type conversion from `NUMBER` to `VARCHAR2`, by adding a new column, transferring data, and renaming it, you can effectively change the column data type without data loss.
This approach is simple, safe, and widely used by Oracle DBAs and developers.