SQL & PL/SQL

Hi all,

I am having problems with using the replace function on clob data in pl/sql. If the clob > 32K large and the replace is on the 32K border it seems to fail.

This issue was addressed before on the forum in this post: https://forums.oracle.com/thread/411338, but there are no answers to that post.

I have simplified the code a bit to focus on the issue:

declare
  vclob  clob;
begin
  vclob := rpad('*', 32749, '*') || '****12345./~\.12345*' || rpad('*', 10000, '*');
  dbms_output.put_line('Original clob text at pos 32746  : ' || substr(vclob, 32746, 50));
  for ch in 1..5 loop
   dbms_output.put_line('Clob after replacing ' || ch || ' with ABCDE: ' || substr(replace(vclob, to_char(ch), 'ABCDE'), 32746, 50));
   end loop;
end;

On my machine the output is:

Original clob text at pos 32746  : ********12345./~\.12345***************************
Clob after replacing 1 with ABCDE: ********ABCDE2345./~\.ABCDE2345*******************
Clob after replacing 2 with ABCDE: ********1ABCDE345./~\.㄀㈀㌀5************************
Clob after replacing 3 with ABCDE: ********12ABCDE45./~\.㄀㈀㌀5************************
Clob after replacing 4 with ABCDE: ********123ABCDE5./~\.㄀㈀㌀5************************
Clob after replacing 5 with ABCDE: ********1234ABCDE./~\.1234ABCDE*******************

It clearly shows an issue with replacing 2, 3 and 4.

I am on 10g Enterprise Edition Release 10.2.0.4.0

Does anyone know of a good work-around or fix? Or should I write my own replace function

Thanks!

Rob

edit: i still need to get used to the new editor...