CODEWITHSUNDEEP
sqloracle-databaseddlalter-table
Vytsalo
Vytsalo

Reputation: 748

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

I have the following columns in my table:

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?

Upvotes: 1

Views: 15832

Answers (3)

gsalem
gsalem

Reputation: 2028

If your table is very large, it would be faster to use dbms_redefinition to do it. Here's an example of using it.

Upvotes: 1

Naveen Kumar
Naveen Kumar

Reputation: 2006

You can achieve this by executing the following steps.

1. Add new column NUMBER1 with type integer.
2. Update table set NUMBER1=TO_NUMBER(NUMBER);
3. Remove column NUMBER.
4. Rename column NUMBER1 to NUMBER.

Upvotes: 2

Popeye
Popeye

Reputation: 35900

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!!

Upvotes: 8

Related Questions