Home » SQL & PL/SQL » SQL & PL/SQL » How to convert Varchar2 to CLOB (toad 9.1 ,oracle 10g)
How to convert Varchar2 to CLOB [message #309621] Fri, 28 March 2008 03:29 Go to next message
ram anand
Messages: 244
Registered: February 2008
Location: india
Senior Member
Hi,
How to convert a varchar2 column to CLOB when there is a thousands of records in it any idea pls can any one help me

Thanks,
Ram.
Re: How to convert Varchar2 to CLOB [message #309624 is a reply to message #309621] Fri, 28 March 2008 03:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
One way is:
SQL> create table t (col varchar2(100));

Table created.

SQL> insert into t values('My name is Michel');

1 row created.

SQL> commit;

Commit complete.

SQL> alter table t add (col2 clob);

Table altered.

SQL> update t set col2 = col;

1 row updated.

SQL> alter table t drop column col;

Table altered.

SQL> alter table t rename column col2 to col;

Table altered.

SQL> set lines 65
SQL> desc t
 Name                             Null?    Type
 -------------------------------- -------- ----------------------
 COL                                       CLOB

SQL> select * from t;
COL
-----------------------------------------------------------------
My name is Michel

1 row selected.

Another way is to use dbms_redefinition package.

Regards
Michel
Re: How to convert Varchar2 to CLOB [message #309632 is a reply to message #309624] Fri, 28 March 2008 04:11 Go to previous messageGo to next message
ram anand
Messages: 244
Registered: February 2008
Location: india
Senior Member
Hi,

Thanks a lot Mr.Michel can u pls give one example for DBMS_REDEFINITION reg the convertion of Varchar2 to CLOB

Thanks,
Ram.
Re: How to convert Varchar2 to CLOB [message #309635 is a reply to message #309632] Fri, 28 March 2008 04:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is a rather complex way.
See Redefining Tables Online for a couple of examples.

Regards
Michel
Re: How to convert Varchar2 to CLOB [message #309644 is a reply to message #309635] Fri, 28 March 2008 05:23 Go to previous messageGo to next message
ram anand
Messages: 244
Registered: February 2008
Location: india
Senior Member
Hi,
can u try it for me pls
Thanks,
Ram.
Re: How to convert Varchar2 to CLOB [message #309656 is a reply to message #309644] Fri, 28 March 2008 05:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It's not my job but yours and I have no time for it.
Try it for you and post the result here for others.

Regards
Michel

[Updated on: Fri, 28 March 2008 05:50]

Report message to a moderator

Re: How to convert Varchar2 to CLOB [message #309663 is a reply to message #309656] Fri, 28 March 2008 05:51 Go to previous messageGo to next message
ram anand
Messages: 244
Registered: February 2008
Location: india
Senior Member
Hi,
ok fine I am sorry for the above send messsage ,i will be trying and post for this convertion of varchar2 to clob using dbms_defentition

Thanks,
RAM.
Re: How to convert Varchar2 to CLOB [message #309666 is a reply to message #309656] Fri, 28 March 2008 06:22 Go to previous messageGo to next message
ram anand
Messages: 244
Registered: February 2008
Location: india
Senior Member
Hi,

--now are the following steps to be followed for the conversion of Varchar2 to CLOB using DBMS_REDEFINITION

1.--original table
create table sample1 (col varchar2(1000));

insert into sample1 values('abcdefghijklmnopqrstuvwxyz');

--interim table (to which we are going to convert)
create table sample2(col clob);

-- here are the following Procedures to be followed

2. DBMS_REDEFINITION.CAN_REDEF_TABLE - used to check whether the table to be redefined (optional)

	BEGIN
	DBMS_REDEFINITION.CAN_REDEF_TABLE('SCOTT','SAMPLE1',dbms_redefinition.cons_use_rowid);
	END;
        /
3.DBMS_REDEFINITION.START_REDEF_TABLE - used to start the redefinition of the specfied table 
	 
	BEGIN
	DBMS_REDEFINITION.START_REDEF_TABLE('SCOTT', 'SAMPLE1','SAMPLE2','to_clob(col) col',dbms_redefinition.cons_use_rowid);
	END;
	/
4.DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS - used to start the copy of the table 

	declare
	 error_count pls_integer := 0;
	BEGIN
	  dbms_redefinition.copy_table_dependents('SCOTT', 'SAMPLE1', 'SAMPLE2',
	                                          1, true,true,true,false,
	                                          error_count);
	
	  dbms_output.put_line('errors := ' || to_char(error_count));
	END;
	/
	
5.DBMS_REDEFINITION.SYNC_INTERIM_TABLE 

 	BEGIN 
	DBMS_REDEFINITION.SYNC_INTERIM_TABLE('SCOTT', 'SAMPLE1', 'SAMPLE2');
	END;
	/

6.DBMS_REDEFINITION.FINISH_REDEF_TABLE - finally finishing of the redefinition of table

	BEGIN
	DBMS_REDEFINITION.FINISH_REDEF_TABLE('SCOTT', 'SAMPLE1', 'SAMPLE2');
	END;
	/


7.drop the interm table (sample2)

8. desc sample1
TABLE sample1
 Name                                      Null?    Type                        
 ----------------------------------------- -------- ----------------------------
 COL                                                CLOB   



9.select * from sample1;

COL                                                 
----------------------------------------------------
abcdefghijklmnopqrstuvwxyz                          


1 row selected.


could pls check this i have tried and got the answer

thanks,
Ram.
Re: How to convert Varchar2 to CLOB [message #309672 is a reply to message #309666] Fri, 28 March 2008 07:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This seems good, thanks for the feedback.

Regards
Michel
Re: How to convert Varchar2 to CLOB [message #456246 is a reply to message #309666] Sun, 16 May 2010 10:53 Go to previous messageGo to next message
ggauravonline
Messages: 1
Registered: May 2010
Junior Member
Hi Ram,

I need to do a similar exercise where i need to convert a table with >10000000 recs in it.

One col needs to be converted from varchar2 to clob.

Does this method work fast ?

Thanks
GAG
Re: How to convert Varchar2 to CLOB [message #456248 is a reply to message #456246] Sun, 16 May 2010 11:43 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>Does this method work fast ?
YES, for some definition of fast.
Re: How to convert Varchar2 to CLOB [message #456381 is a reply to message #456248] Mon, 17 May 2010 10:18 Go to previous messageGo to next message
asmani
Messages: 47
Registered: February 2007
Member
No Message Body

[Updated on: Mon, 17 May 2010 10:20]

Report message to a moderator

Re: How to convert Varchar2 to CLOB [message #456384 is a reply to message #456381] Mon, 17 May 2010 10:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks for the feedback.

Regards
Michel

[Updated on: Mon, 17 May 2010 10:21]

Report message to a moderator

Re: How to convert Varchar2 to CLOB [message #456386 is a reply to message #456381] Mon, 17 May 2010 10:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Hey! Why did you erase your post?

Regards
Michel
Re: How to convert Varchar2 to CLOB [message #456387 is a reply to message #456384] Mon, 17 May 2010 10:23 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Message deleted ?
sriram Smile
Re: How to convert Varchar2 to CLOB [message #456388 is a reply to message #456387] Mon, 17 May 2010 10:24 Go to previous message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Ok I see his answer in another CLOB topic which is more appropriate to it.

Regards
Michel
Previous Topic: Divide and Conquer approach
Next Topic: Can I insert CLOB in varchar2?
Goto Forum:
  


Current Time: Fri Dec 09 14:01:33 CST 2016

Total time taken to generate the page: 0.08038 seconds