OCP Blog
Super DBA
How to convert LONG column to CLOB
by Vazha Mantua Wednesday, December 28, 2011 4:24 PM

Today We will show 2 methods how convert long column to clob.

METHOD I: Simple method using TO_LOB procedure:

CREATE TABLE NBA_LONG (ID NUMBER, TEXT_L LONG);
CREATE TABLE NBA_CLOB (ID NUMBER, TEXT_C CLOB);

INSERT INTO NBA_LONG VALUES (1, 'LONG SHOULD BE CONVERTED TO CLOB');
INSERT INTO NBA_CLOB SELECT ID, TO_LOB(TEXT_L) FROM NBA_LONG;
COMMIT;

As you see in table NBA_CLOB column text_c contain information of column text_l from table NBA_LONG.

----


METHOD II: With using DBMS_REDEFINITION package


CREATE TABLE SCOTT.NBA_LONG (ID NUMBER PRIMARY KEY, TEXT_L LONG);
CREATE TABLE SCOTT.NBA_CLOB (ID NUMBER, TEXT_C CLOB);
INSERT INTO SCOTT.NBA_LONG VALUES (1, 'LONG SHOULD BE CONVERTED TO CLOB');
COMMIT;

 

DECLARE
VM BINARY_INTEGER;
BEGIN
--VERIFY THAT THE TABLE NBA_LONG IS A CANDIDATE FOR ON LINE REDEFINITION
DBMS_REDEFINITION.CAN_REDEF_TABLE('SCOTT','NBA_LONG');
--START THE REDEFINITION PROCESS
DBMS_REDEFINITION.START_REDEF_TABLE('SCOTT','NBA_LONG','NBA_CLOB','ID ID,TO_LOB(TEXT_L) TEXT_C', DBMS_REDEFINITION.CONS_USE_PK);
--AUTOMATICALLY CREATE ANY TRIGGERS, INDEXES AND CONSTRAINTS ON NBA_CLOB
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('SCOTT','NBA_LONG','NBA_CLOB', DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, FALSE, VM);
--OPTIONALLY FOR SYNCHRONIZE
DBMS_REDEFINITION.SYNC_INTERIM_TABLE('SCOTT', 'NBA_LONG','NBA_CLOB');
-- COMPLETE THE REDEFINITION
DBMS_REDEFINITION.FINISH_REDEF_TABLE('SCOTT', 'NBA_LONG','NBA_CLOB');
END;

Tags: , , , , , , ,

Comments (6) -

1/7/2012 4:57:17 PM #

Villas for Rent in Italy

Great information thank you so very much for sharing!

Villas for Rent in Italy United States

1/12/2012 1:16:25 PM #

Boutique PR Agency

I used to be very happy to find this web-site.I wanted to thanks to your time for this wonderful learn!! I definitely enjoying each little bit of it and I have you bookmarked to check out new stuff you blog post.

Boutique PR Agency United States

3/29/2012 1:27:20 PM #

free newspaper classified ads

I am looking forward to hearing more from you.

free newspaper classified ads United States

4/11/2012 1:56:17 AM #

Challenge Coins

I was having little idea about that but now I am too sure how to do this thing in actual way.

Challenge Coins United States

6/6/2012 10:06:44 PM #

Football Player Transfers

good content, I found your site too good, I was thinking to have such blogs in my search and I got here, considering myself so luck .

Football Player Transfers United States

8/2/2012 12:11:00 PM #

Itchy Scalp Treatment

The arcane LONG datatype has as you’re aware of many intrinsic limitations but unfortunately Oracle still uses quite a few LONG columns in the data dictionary. It’s beyond me why these haven’t been converted to CLOB a very long time ago.

Itchy Scalp Treatment United States

Add comment

  Country flag

biuquote
  • Comment
  • Preview
Loading

Filter by APML

Calendar

<<  November 2017  >>
MoTuWeThFrSaSu
303112345
6789101112
13141516171819
20212223242526
27282930123
45678910

View posts in large calendar

TextBox