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));
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;
/
Results:
v2:32767
cl_small:32767
cl_big[1]:65535
cl_big[2]:98301
ORA-22828: input pattern or replacement parameters exceed 32K size limit
This seems at odds with the docs which imply that the replacement string may be a CLOB - I would have thought this should imply that any CLOB would be allowed, not just those that happen to be <32K: http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions153.htm#SQLRF00697
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