ORA-22828 input style or replacement parameter exceeds 32 K size limit

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!