How to call REPLACE with CLOB (without exceeding 32K)

Question

Oracle 11g has certainly improved usability of CLOBs, having overloaded most of the string functions so they now work natively with CLOBs. However, a colleague was getting this error from his code: ``` ORA-22828: input pattern or replacement parameters exceed 32K size limit 22828. 00000 - "input pattern or replacement parameters exceed 32K size limit" *Cause: Value provided for the pattern or replacement string in the form of VARCHAR2 or CLOB for LOB SQL functions exceeded the 32K size limit. *Action: Use a shorter pattern or process a long pattern string in multiple passes. ``` This only occurred when the third parameter to `replace` was a CLOB with more than 32k characters. (Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production) Test case: ``` declare v2 varchar2(32767); cl_small clob; cl_big clob; cl_big2 clob; begin v2 := rpad('x', 32767, 'x'); dbms_output.put_line('v2:' || length(v2)); cl_small := v2; dbms_output.put_line('cl_small:' || length(cl_small)); c…

Accepted Answer

Here is a rough first draft for a function that will do the job with certain limitations, it hasn't been very well tested yet:

```
function replace_with_clob
  (i_source in clob
  ,i_search in varchar2
  ,i_replace in clob
  ) return clob is
  l_pos pls_integer;
begin
  l_pos := instr(i_source, i_search);
  if l_pos > 0 then
    return substr(i_source, 1, l_pos-1)
        || i_replace
        || substr(i_source, l_pos+length(i_search));
  end if;
  return i_source;
end replace_with_clob;
```

It only does a single replace on the first instance of the search term.

```
declare
  v2 varchar2(32767);
  cl_small clob;
  cl_big clob;
  cl_big2 clob;
begin
  v2 := rpad('x', 32767, 'x');
  dbms_output.put_line('v2:' || length(v2));
  cl_small := v2;
  dbms_output.put_line('cl_small:' || length(cl_small));
  cl_big := v2 || 'y' || v2;
  dbms_output.put_line('cl_big[1]:' || length(cl_big));
  cl_big2 := replace(cl_big, 'y', cl_small);
  dbms_output.put_line('cl_big[2]:' || length(cl_big2));
  cl_big2 := replace_with_clob(cl_big, 'y', cl_big); 
  dbms_output.put_line('cl_big[3]:' || length(cl_big2));
end;
/

v2:32767
cl_small:32767
cl_big[1]:65535
cl_big[2]:98301
cl_big[3]:131069
```

How to call REPLACE with CLOB (without exceeding 32K)

Solution

Problem