title: How to call REPLACE with CLOB (without exceeding 32K)
body: <p>Oracle 11g has certainly improved usability of CLOBs, having overloaded most of the string functions so they now work natively with CLOBs.</p>
  
  <p>However, a colleague was getting this error from his code:</p>
  
  <pre><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.
  </code></pre>
  
  <p>This only occurred when the third parameter to <code>replace</code> was a CLOB with more than 32k characters.</p>
  
  <p>(Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production)</p>
  
  <p>Test case:</p>
  
  <pre><code>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(cl_big, 'y', cl_big); 
    dbms_output.put_line('cl_big[3]:' || length(cl_big2));
  end;
  /
  </code></pre>
  
  <p>Results:</p>
  
  <pre><code>v2:32767
  cl_small:32767
  cl_big[1]:65535
  cl_big[2]:98301
  ORA-22828: input pattern or replacement parameters exceed 32K size limit
  </code></pre>
  
  <p>This seems at odds with the docs which imply that the replacement string may be a CLOB - I would have thought this <em>should</em> imply that any CLOB would be allowed, not just those that happen to be &lt;32K: <a href="http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions153.htm#SQLRF00697" rel="noreferrer">http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions153.htm#SQLRF00697</a></p>