Changing data types of columns...

Today I came across one good example

You have one table say ABC, and in that you have 4 cols which are having data type as varchar2(200).

Requirement is , you need to convert the data types of these columns to Number. This table has a data.

How will you change the data types of these columns ?

Note:- You can not create backup table in production...

----------------

CREATE TABLE ABC(COL1 VARCHAR2(200),COL2 VARCHAR2(200),COL3 VARCHAR2(200),COL4

SET DEFINE OFF;
Insert into HR.ABC
   (COL1, COL2, COL3, COL4)
 Values
   ('1', '1', '1', '1');
Insert into HR.ABC
   (COL1, COL2, COL3, COL4)
 Values
   ('2', '2', '2', '2');
Insert into HR.ABC
   (COL1, COL2, COL3, COL4)
 Values
   ('3', '3', '3', '3');
COMMIT;

---------------

TABLE ABCADD

UPDATE (COL11 NUMBER, COL22 NUMBER, COL33 NUMBER, COL44 NUMBER)
---------------

ABCSET COL44 = TO_NUMBER(COL4)
ALTER

UPDATE ABC SET COL33 = TO_NUMBER(COL3)

UPDATE ABC SET COL22 = TO_NUMBER(COL2)


UPDATE ABC SET COL11 = TO_NUMBER(COL1)
----------------

ALTER  TABLE ABC DROP(COL1, COL2, COL3, COL4)
----------------

ALTER TABLE ABC RENAME column COL44 TO COL4

ALTER TABLE ABC RENAME column COL33 TO COL3

ALTER TABLE ABC RENAME column COL22 TO COL2

ALTER TABLE ABC RENAME column COL11 TO COL1


---------------

1. Here, you need to create dummy columns with number datatype at abc, because you can not create column with same name again.
2. You converted data using to_number from varchar to number and updated the newly added columns
3. You dropped the old columns
4. You rename newly added columns