This short article presents how to solve ORA-22828 error.

ORA-22828 happens when you try to replace part of a CLOB with a CLOB bigger than 32K using standard functions like REPLACE, REGEXP_REPLACE.

Following call of REPLACE function works fine when part of CLOB is replaced with CLOB 32K

DECLARE
  l_clob_in    CLOB := 'simple sentence';
  l_clob_32k    CLOB := rpad('x', 32767, 'x');
  l_clob_out   CLOB;
BEGIN
  l_clob_out := REPLACE(l_clob_in, 'sentence', l_clob_32k);
END;
/

PL/SQL procedure successfully completed.

but for CLOB bigger than 32K is raises error ORA-22828

DECLARE
  l_clob_in    CLOB := 'simple sentence';
  l_clob_32k   CLOB := rpad('x', 32767, 'x');
  l_clob_32k1  CLOB := l_clob_32k || 'x';
  l_clob_out   CLOB;
BEGIN
  l_clob_out := REPLACE(l_clob_in, 'sentence', l_clob_32k1);
END;
/

ORA-22828: input pattern or replacement 
           parameters exceed 32K size limit

Solution is to write own replace function that works with CLOB bigger than 32K

CREATE OR REPLACE FUNCTION replace_clob
(
  in_source  IN CLOB,
  in_search  IN VARCHAR2,
  in_replace IN CLOB
) 
RETURN CLOB 
IS
  l_pos pls_integer;
BEGIN
  l_pos := instr(in_source, in_search);

  IF l_pos > 0 THEN
    RETURN substr(in_source, 1, l_pos-1)
        || in_replace
        || substr(in_source, l_pos+LENGTH(in_search));
  END IF;

  RETURN in_source;
END replace_clob;
/

DECLARE
  l_clob_in    CLOB := 'simple sentence';
  l_clob_32k   CLOB := rpad('x', 32767, 'x');
  l_clob_32k1  CLOB := l_clob_32k || 'x';
  l_clob_out   CLOB;
BEGIN
  l_clob_out := replace_clob(l_clob_in, 'sentence', l_clob_32k1);
END;
/

PL/SQL procedure successfully completed.

Have a fun 🙂

Tomasz