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
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
No comments:
Post a Comment