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

3 comments:

  1. Hi! That helps me a lot. Great thanks!

    ReplyDelete
  2. Good work Mayo. Just one enhancement to this which we came across recently is the last dbms_lob.copy statement should be wrapped with an IF statement to check that there is still any further data to copy otherwise the third argument results in 0 which is an illegal input value. This scenario can happen when the p_what is at the end of the clob stream.

    ReplyDelete
  3. That's a fairly heavyweight implementation.. This is technically equivalent and is less resource intensive.

    This works on 11g (I haven't tried others.. your mileage may vary)

    Sorry about formatting.. Can't seem to get this blog to accept a formatting tag.

    FUNCTION replace_with_clob
    (
    i_source IN CLOB
    ,i_search IN VARCHAR2
    ,i_replace IN CLOB
    ) RETURN CLOB IS
    l_pos PLS_INTEGER;
    l_prev_pos PLS_INTEGER := 1;
    l_return CLOB := i_source;
    BEGIN
    WHILE TRUE
    LOOP
    l_pos := instr(l_return, i_search, l_prev_pos);

    IF l_pos > 0
    THEN
    l_prev_pos := l_pos + 1;
    l_return := substr(l_return, 1, l_pos - 1) ||
    i_replace ||
    substr(l_return
    ,l_pos + length(i_search));
    ELSE
    RETURN l_return;
    END IF;
    END LOOP;
    END replace_with_clob;

    ReplyDelete