Hello,
I am trying to read a line at a time from a clob for processing. I have the following code, which works fine as long as the clob does not have too many lines. Any idea, how I can do this? Right now, what i am doing is reading a line from the clob, the putting it in another clob minus the line that was selected and then do a erase and a trim on the original clob and finally write back to the original clob from the second clob. Like I said it works, but not for a big file. Any idea any one? Thanks in advance for your help.
Here is the code....
declare
fill clob;
new_clob clob;
chrlocation integer;
line_length integer;
clob_length integer;
new_length integer;
line varchar2(2000);
pos integer;
line_counter integer;
new_clob_size integer;
begin
pos := 1;
chrlocation := -1;
line_length := -1;
clob_length := -1;
new_length := -1;
line_counter := 1;

loop
SELECT clob_message INTO fill FROM import_clobs WHERE import_clob_id = 1931 for update;
--select clob_text into fill from clob_test where id = 3 for update;
if dbms_lob.isopen(fill) <> 0 then
dbms_lob.close(fill);
end if;

dbms_lob.open(fill, dbms_lob.lob_readwrite);

chrlocation := dbms_lob.instr(fill, chr(10));
clob_length := dbms_lob.getlength(fill);

line := dbms_lob.substr(fill, chrlocation);
line_length := length(line);

chrlocation := chrlocation + 1;
new_clob_size := clob_length - line_length;

new_clob := dbms_lob.substr(fill, new_clob_size, chrlocation);

dbms_output.put_line('chrlocation = ' || chrlocation);
dbms_output.put_line('clob_length = ' || clob_length);
dbms_output.put_line(substr(line, 1, 250));
--dbms_output.put_line('new_clob = ' || substr(new_clob, 1, 240));
dbms_output.put_line('line_length = ' || line_length);
dbms_output.put_line('line_Counter = ' || line_counter);
dbms_output.put_line(' ');
dbms_output.put_line(' ');
dbms_output.put_line(' ');

dbms_lob.erase(fill, clob_length, 1);

dbms_lob.trim(fill, 0);

if new_clob_size < 1 then
if dbms_lob.isopen(fill) <> 0 then
dbms_lob.close(fill);
end if;
exit;
end if;

dbms_lob.write(fill, new_clob_size, 1, new_clob);

dbms_lob.erase(new_clob, new_clob_size, 1);

dbms_lob.trim(new_clob, 0);

if dbms_lob.isopen(fill) <> 0 then
dbms_lob.close(fill);
end if;

commit;

line_counter := line_counter + 1;

exit when line_counter = 10000;

end loop;

exception
WHEN no_data_found
THEN
BEGIN
dbms_output.put_line(substr(SQLERRM, 1, 200));
if dbms_lob.isopen(fill) <> 0 then
dbms_lob.close(fill);
end if;
END;
-- or dbms_lob.filecloseall if appropriate
WHEN OTHERS THEN
dbms_output.put_line(substr(SQLERRM, 1, 200));
if dbms_lob.isopen(fill) <> 0 then
dbms_lob.close(fill);
end if;
end;