Oracle – REPLACE() for CLOBs

In earlier versions of Oracle, the REPLACE() function did not work natively with CLOBs. By implicitly casting to VARCHAR2s, this function would work, but failed when a CLOB exceeded the maximum VARCHAR2 length. REPLACE() now works directly with CLOBs.

Although it is now antiquated, the function below is an example of one approach to perform a search and replace on a CLOB without actually using the REPLACE() function. It may be useful on older instances of Oracle, but is also a good example of some of the basic DBMS_LOB functions that are still commonly used. It is also a launch-point for more advanced processing.

As a side note, I have seen a number of functions that perform similarly to this function, by simply breaking the CLOB into a series of VARCHAR2 strings and performing a REPLACE() on each of the chunks. The issue with this approach is that it does not address boundary conditions when the search string is split between two of these chunks.

CREATE OR REPLACE
FUNCTION clob_replace( i_src CLOB, i_match VARCHAR2, i_rep VARCHAR2 )
RETURN CLOB
IS
  MAX_VARCHAR2 NUMBER DEFAULT 15000;  -- depending on your character set, 32767 may not be your actual number of available characters

  l_ret_clob CLOB;                    -- temporary CLOB to be returned
  l_next_idx NUMBER;                  -- used to traverse the source CLOB, captures the next instance of the match string
  l_search_begin NUMBER DEFAULT 1;    -- used to traverse the source CLOB, indicates the starting point of the next search
  l_parse_begin NUMBER DEFAULT 1;     -- used to copy date from the source CLOB, indicates the beginning of the copy window
  l_parse_end NUMBER;                 -- used to copy date from the source CLOB, indicates the beginning of the end window

BEGIN

  -- Create a temporary CLOB to be returned
  DBMS_LOB.CREATETEMPORARY( l_ret_clob, TRUE );

  LOOP

    l_next_idx := DBMS_LOB.INSTR( i_src, i_match, l_search_begin, 1 );

    -- Exit once we do not find another occurance of the match string
    EXIT WHEN l_next_idx = 0;

    -- Special handling if we find a match at the first character of the source string
    IF l_next_idx = 1 THEN

      l_parse_begin := LENGTH( i_match ) + 1;

      IF LENGTH( i_rep ) > 0 THEN
        DBMS_LOB.APPEND( l_ret_clob, i_rep );
      END IF;

    ELSE
 
      -- Copy source data up to the match
      l_parse_end := l_next_idx - 1;
      DBMS_LOB.COPY( l_ret_clob, i_src, ( l_parse_end - l_parse_begin + 1 ), DBMS_LOB.GETLENGTH( l_ret_clob ) + 1, l_parse_begin );
      
      -- Replace the match string
      IF LENGTH( i_rep ) > 0 THEN
        DBMS_LOB.APPEND( l_ret_clob, i_rep );
      END IF;

      -- Move the beginning of the next parse window past our match string
      l_parse_begin := l_parse_end + LENGTH( i_match ) + 1;

    END IF;

    -- Move the beginning of the search window past our match string
    l_search_begin := l_next_idx + LENGTH( i_match );

  END LOOP;


  -- Special handling for the trailing final characters of the source CLOB
  IF DBMS_LOB.GETLENGTH( i_src ) > ( l_parse_end  + LENGTH( i_match ) ) THEN

    l_parse_begin := l_parse_end  + LENGTH( i_match ) + 1;
    l_parse_end := DBMS_LOB.GETLENGTH( i_src );
    DBMS_LOB.COPY( l_ret_clob, i_src, ( l_parse_end - l_parse_begin + 1 ), DBMS_LOB.GETLENGTH( l_ret_clob ) + 1, l_parse_begin );

  END IF;

  RETURN l_ret_clob;

END;
/
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s