Oracle - Replacing the Replace function

[abhijit]

This native function in Oracle has a 4000 byte limit. This function extends that limit to 32000 bytes.

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;