ORA-22828 input style or replacement parameter exceeds 32 K size limit
The following error is reported during program debugging today:
ORA-22828: input pattern or replacement parameters exceed 32K size limit22828. 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.
The following script can be used to reproduce the data:
declare cb clob; ret_cb CLOB;begin cb := rpad('x', 32767, 'x')||'x'; ret_cb := '<resp>#result#</resp>'; ret_cb := replace(ret_cb, '#result#', cb); end;/
The solution is as follows:
Compile functions:
create or replace 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;
After Rewriting:
declare cb clob; ret_cb CLOB;begin cb := rpad('x', 32767, 'x')||'x'; ret_cb := '<resp>#result#</resp>'; ret_cb := replace_with_clob(ret_cb, '#result#', cb); dbms_output.put_line(substr(ret_cb, 1, 100));end;/
Execution successful!