Error with procedure to replace in clobs

[fatcodeguy]

Hi, I have a table with a CLOB column. some of the CLOBs have string segments that I need to replace, so I wrote a procedure that will get every clob for the column that has this segment that needs to be replaced and update it.

My proc works by creating a temporary clob, reading the clob from the table into the buffer, replacing the string if it exists in the buffer, and then writing the buffer to the temporary clob. Then i update the table with the new temp clob.

My problem is that for some of the clobs (in no pattern that I can discern) the last character is ommitted.

Here's my procedure, can anyone help me?

procedure replace_clob(p_id sp_doc.id%type) IS

   v_enclob CLOB := EMPTY_CLOB;
   v_new_clob CLOB := EMPTY_CLOB;

   v_buffer   VARCHAR2 (32767);
   l_amount   BINARY_INTEGER := 32767;
   l_pos      PLS_INTEGER := 1;
   l_clob_len PLS_INTEGER;

   cursor clobs is
   select id,display_order,enbody,frbody
   from sp_doc
   where id=p_id
   order by display_order;

BEGIN

	for v in clobs loop

		--get enbody into variable
		v_enclob:=v.enbody;
		--initialize the new clob
		dbms_lob.CreateTemporary(v_new_clob,TRUE);
		--reset other values
		v_buffer:='';
		l_pos:=1;
		--get the length of the clob
		l_clob_len := dbms_lob.getlength(v_enclob);

		while l_pos < l_clob_len loop

			  --read from source, into the buffer
			  dbms_lob.read(v_enclob, l_amount, l_pos, v_buffer);

			  if v_buffer is not null then

			  	 --replace the text we want to replace
			  	 v_buffer:=replace(v_buffer,'find_str','replace_str');

				 --write to the new clob
				 dbms_lob.writeAppend(v_new_clob, length(v_buffer), v_buffer);

			  end if;
			  --increment counter
			  l_pos := l_pos + l_amount;
			  --clear buffer
			  v_buffer:='';
		end loop;

		--now we have the new clob ... update the table
		update sp_doc set enbody=v_new_clob where id=v.id and display_order=v.display_order;
	end loop;

EXCEPTION
 WHEN others THEN
    dbms_output.put_line('ERROR');

end;

Thanks

 

[sem]

Your error is quite common. The substring may be stripped accross multiple buffers. E.g. you need to find 'XX' and your CLOB contains 32766 'Y' and then 'XX'. In this case none of buffers contains 2 consecutive X's. Your procedure should be more robust (e.g. move pointer back by the length of substring). BTW what is your Oracle version? Why don't you use DBMS_LOB package? Besides in Oracle 9+ you may manipulate LOB's the same way as VARCHAR2's.

Regards, Dima

http://seminihin.narod.ru

 

[fatcodeguy]

Thanks, I got that error fixed. Now I'm wondering if there's not a simpler way.

I'm using Oracle 8, so i can't treat the clob as a varchar, and I'm using the dbms_lob package instead.

Is there an easier way to replace a string in the clob (which appears in the first 100 chars, and nowhere else) without having to write a new clob and update the table with the new clob?