How to convert Varchar2 to CLOB [message #309621] |
Fri, 28 March 2008 03:29  |
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 #309666 is a reply to message #309656] |
Fri, 28 March 2008 06:22   |
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.
|
|
|
|
|
|
|
|
|
|
|