Changing datatype of column from varchar2 to number when data present in that column

You can use the Oracle “alter table” syntax to change the data type for an existing column, as shown in this example:

alter table
   table_name
modify
(
   column_name    varchar2(30)
);

Of course, you must deal with existing column data.  When modifying a tables column datatype you may want to export the rows, redefine the table and then re-import you data.

Following is the one scenario where we get ORA-01722 error while changing existing columns datatype from varchar2 to number, here table contains the lacks of records.

Column that we are going to modify datatype contains only numeric records  but that data was inserted when its datatype was varchar2. Now it is challenge to modify this columns datatype. 

select * from xyz 
Suppose above table contains some records.

Create the backup table for above table by executing following command

create table xyz_bkp as select * from xyz

Modify the column using following command

alter table xyz_bkp modify abc number(20,4)

You may get the ORA-01722 error

Then check by adding new column to that table and then try to update all the records from existing column.

alter table xyz_bkp add abc_1(15,2);

update xyz_bkp

set abc_1=abc;

Here also we received the ORA-01722 error.

Now lets know about this error message.

ORA-01722 is thrown because a particular string was not able o be converted into a specific valid number when a user attempted to convert a character string. 

There are several possible resolutions to Oracle ORA-01722 in this context:

• If you are attempting an " INSERT INTO ... VALUES (...) " you need to find out which data item is invalid
• If you are trying to supply the values in a sub query which is intended to INSERT or UPDATE, you have to dig a little beeper because the character that is throwing ORA-01722 is actually hidden.  You would then need to find the row that contains a non-numeric string.  Then, you should fix or add data to resolve ORA-01722
• Instead of an INSERT or UPDATE, you attempt a SELECT.  Here, ORA-01722 is thrown most likely because of some implicit conversation in WHERE.  To resolve ORA-01722 in this context, find a numeric and character column which are being compared.

I used the following method to remove character string or any other characters other than number from that column.

update xyz_bkp

set abc_1= RTRIM

          ((LTRIM

               (TRANSLATE

                   (REPLACE (abc, 'A', ''),

                       'ABCDEFGHIJKLMNOPQRSTUVWXYZ

abcdefghijklmnopqrstuvwxyz@#$%^&*()_+-=,!\`~{}./?:";''[]'

                    || CHR (160)

                    || CHR (32)

                    || CHR (0)

                    || CHR (253),

                    'A'

                   )

               )

           )

          )      

This statement will remove the special characters or character string from abc column.

Now remove or drop the existing column 

alter table xyz_bkp drop column abc;

Rename new column with existing column's name

alter table xyz_bkp rename column abc_1 to abc

If all the above steps are successful the proceed to do same in your main table.