Tuesday 30 July 2013

Replace varchar in CLOB with CLOB

I was trying to do a replace within a CLOB. The problem I ran into was that either the original CLOB, the replacement CLOB or the final result would exceed the 32k limit for oracle String functions like REPLACE. Here is some code that allows you to replace a part of a CLOB with another CLOB no matter the size.
It uses dbms_lob.copy

FUNCTION replace_clob( p_lob IN CLOB,
                       p_what IN VARCHAR2,
                       p_with IN CLOB )
RETURN CLOB
AS
    n NUMBER;
    l_result CLOB := p_lob;
BEGIN  
    n := dbms_lob.INSTR( p_lob, p_what);  
    IF ( NVL(n,0) > 0 )
    THEN
            dbms_lob.createtemporary(l_result, FALSE, dbms_lob.CALL);
            dbms_lob.copy(l_result, p_lob, n - 1, 1, 1);
            dbms_lob.copy(l_result,
                          p_with,
                          dbms_lob.getlength(p_with) ,
                          dbms_lob.getlength(l_result) + 1,
                          1 );
            dbms_lob.copy(l_result,
                          p_lob,
                          dbms_lob.getlength(p_lob) - (n + LENGTH(p_what)) + 1 ,
                          dbms_lob.getlength(l_result) + 1,
                          n + LENGTH(p_what) );
    END IF;
       
    IF NVL(dbms_lob.INSTR(l_result, p_what), 0) > 0 THEN
        RETURN replace_clob(l_result, p_what, p_with);
    END IF;
   
    RETURN l_result;
   
END;

Hope this helps as I couldn't find this particular solution anywhere else