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 32 K size limit
22828. 00000-"input pattern or replacement parameters exceed 32 K size limit"
* Cause: Value provided for the pattern or replacement string in the form
VARCHAR2 or CLOB for lob SQL functions exceeded the 32 K 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
Rochelle POS pls_integer;
Begin
Rochelle 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!

-----------------------------------

Dylan Presents.