Oracle: How to change column data type from VARCHAR to NUMBER without losing data

I have the following columns in my table:

• ID - NUMBER(10,0)
• NUMBER - VARCHAR(255)

All data in the NUMBER column are numbers. I would like to change the VARCHAR type to Integer Number type. How to do it without data loss?

Answer

Oracle does not allow modification of data type of the column if it is not empty so as a workaround, You need to follow the following steps

1. create another column with NUMBER data type let’s say "NUMBER1".
2. add the data of "NUMBER" column into that newly created column("NUMBER1").
3. Remove the original "NUMBER" column
4. Rename the newly created column "NUMBER1" to "NUMBER"

as following:

Oracle setup:

SQL> CREATE TABLE YOUR_TABLE (
  2      ID         NUMBER(10, 0),
  3      "NUMBER"   VARCHAR(255)
  4  );

Table created.


SQL> DESC YOUR_TABLE;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER(10)
 NUMBER                                             VARCHAR2(255)

SQL> INSERT INTO YOUR_TABLE VALUES (1,'1');

1 row created.

SQL> COMMIT;

Commit complete.

Showing error if the column data type is changed directly:

SQL> ALTER TABLE YOUR_TABLE MODIFY "NUMBER" NUMBER;
ALTER TABLE YOUR_TABLE MODIFY "NUMBER" NUMBER
                              *
ERROR at line 1:
ORA-01439: column to be modified must be empty to change datatype

Solution:

SQL> ALTER TABLE YOUR_TABLE ADD "NUMBER1" NUMBER;

Table altered.

SQL>
SQL> UPDATE YOUR_TABLE
  2  SET "NUMBER1" = "NUMBER";

1 row updated.

SQL>
SQL> ALTER TABLE YOUR_TABLE DROP COLUMN "NUMBER";

Table altered.

SQL>
SQL> ALTER TABLE YOUR_TABLE RENAME COLUMN "NUMBER1" TO "NUMBER";

Table altered.

SQL> DESC YOUR_TABLE;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER(10)
 NUMBER                                             NUMBER

SQL>

Cheers!!