Convert Oracle Datatype LONG to NUMBER

How to convert LONG datatype to Number in ORACLE ?

This was the requirement in our organization to convert LONG datatype to Number datatype, It is relatively easy and According to my logic.

— Here C1 Column datatype is LONG , First I Modify with CLOB

ALTER TABLE SCHEMA.TABLE_NAME MODIFY(C1 CLOB);

— Then Add new Column TEST1 with datatype Varchar2

ALTER TABLE SCHEMA.TABLE_NAME ADD (TEST1 VARCHAR2(50));

— Then transfer C1 column data into TEST1 column and commit.

UPDATE SCHEMA.TABLE_NAME SET TEST1=C1;

COMMIT;

— Again add new Column TEST2 with NUMBER datatype

ALTER TABLE SCHEMA.TABLE_NAME ADD (TEST2 NUMBER(20,2));

— Then Copy TEST1 data to TEST2 and commit.

UPDATE SCHEMA.TABLE_NAME SET TEST2=TEST1;

COMMIT;

— Now drop old original column C1

ALTER TABLE SCHEMA.TABLE_NAME DROP COLUMN C1;

— Also drop new column TEST1

ALTER TABLE SCHEMA.TABLE_NAME DROP COLUMN TEST1;

— And last rename the second new column TEST2 to original column name like here C1

ALTER TABLE SCHEMA.TABLE_NAME RENAME COLUMN T2 TO C1;

Advertisements
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Advertisements
%d bloggers like this: