This native function in Oracle has a 4000 byte limit. This function extends that limit to 32000 bytes.
Code:
CREATE OR REPLACE FUNCTION SPCCHAR2(v_Input varchar2, v_REPLACEstr varchar2) return varchar2
IS
/*
Author: Abhijit
Date: 03/20/2009
Purpose: Override the limitation on REPLACE provided by PL/SQL.
*/
TYPE SPECIALCHARS IS TABLE OF VARCHAR2(4000) NOT NULL;
v_Stc SPECIALCHARS :=SPECIALCHARS();
IArrayIndex INTEGER := 1;
v_ReturnString varchar2(32000):='';
v_ProcessString varchar2(32000):='';
BEGIN
v_ProcessString := v_Input;
WHILE Length(v_ProcessString) > 0
LOOP
v_Stc.EXTEND;
v_Stc(IArrayIndex) := Substr(v_ProcessString, 1, 3200);
v_ProcessString := Substr(v_ProcessString, 3201);
IArrayIndex := IArrayIndex + 1;
END LOOP;
FOR Iarrayindex IN 1 .. v_Stc.COUNT
LOOP
v_ReturnString := Trim(v_ReturnString) || REPLACE(v_Stc(iArrayIndex), v_Replacestr);
END LOOP;
return Trim(v_ReturnString);
EXCEPTION
WHEN OTHERS THEN
RAISE;
END;