Hi OTN Members,
I have a requirement to insert the data from CLOB to LONG column.
Source table column description:
source_tbl
(column1 varchar2(30),
column2 varchar2(30),
COMMENTS CLOB );
Destination table column description:
dest_tbl
(column1 varchar2(30),
column2 varchar2(30),
NOTE LONG );
I have to insert data from COMMENTS to NOTE column
I tried to insert the value into NOTE column using two options :
Option 1:
Taking the value of COMMENTS in a cursor and inserting the value into NOTE as below:
declare
cursor c1 is
select comments from <source_tbl>
where <condition 1>
and <condition 2>;
begin
for rec in c1 loop
insert into <dest_tbl> (note) values (rec.comments);
end loop;
Getting the below error:
ORA-22835: Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: 4577, maximum: 4000) . UID = SCM_00Tw000003EpVIX
The cols which have length > 4000 got failed to insert.
Option 2:
declare
l_note long;
l_comments clob
cursor c1 is
select comments from <source_tbl>
where <condition 1>
and <condition 2>;
begin
for rec in c1 loop
l_comments := rec.comments;
l_note := l_comments;
insert into <dest_tbl> (note) values (l_note);
end loop;
Getting below error:
ORA-01461: can bind a LONG value only for insert into a LONG column
Please guide how to insert data into LONG column from CLOB.
P.S.: I can't change the column types of both the source and table columns.
Regards,
Saurabh Mehta