How To Replicate the Replace Built-In for CLOBs

A HunBug Document
Document No.: 11080
Database Version: Oracle 9i
Last Updated: 01 Jan 2006
Author: HunBug
The DBMS_LOB package is very useful but doesn’t come with a replace function. The standard REPLACE built-in is restricted by the PLSQL limit of a VARCHAR2, making it unusable for CLOB data

Examples can be found to perform a replace using a procedure, but these can’t simply be converted to a function as the DBMS_LOB.WRITE procedure can’t be called from a select statement.
The solution here has to avoid these procedures so it can be used as a proper function. Therefore it loops through the CLOB, 32K each time as this is the maximum DBMS_LOB.SUBSTR can return.

The Function

CREATE OR REPLACE FUNCTION dfn_clobReplace
( p_clob          IN CLOB,
  p_what          IN VARCHAR2,
  p_with          IN VARCHAR2 ) RETURN CLOB IS

  c_whatLen       CONSTANT PLS_INTEGER := LENGTH(p_what);
  c_withLen       CONSTANT PLS_INTEGER := LENGTH(p_with);

  l_return        CLOB;
  l_segment       CLOB;
  l_pos           PLS_INTEGER := 1-c_withLen;
  l_offset        PLS_INTEGER := 1;

BEGIN

  IF p_what IS NOT NULL THEN
    WHILE l_offset < DBMS_LOB.GETLENGTH(p_clob) LOOP
      l_segment := DBMS_LOB.SUBSTR(p_clob,32767,l_offset);
      LOOP
        l_pos := DBMS_LOB.INSTR(l_segment,p_what,l_pos+c_withLen);
        EXIT WHEN (NVL(l_pos,0) = 0) OR (l_pos = 32767-c_withLen);
        l_segment := TO_CLOB( DBMS_LOB.SUBSTR(l_segment,l_pos-1)
                            ||p_with
                            ||DBMS_LOB.SUBSTR(l_segment,32767-c_whatLen-l_pos-c_whatLen+1,l_pos+c_whatLen));
      END LOOP;
      l_return := l_return||l_segment;
      l_offset := l_offset + 32767 - c_whatLen;
    END LOOP;
  END IF;

  RETURN(l_return);

END;
/

How It Works
The function accepts three parameters, the same as the standard REPLACE function.
1.    p_clob: The CLOB data
2.    p_what: The search string
3.    p_with: The replacement string

It first checks that the p_what string is not null, to save time checking the CLOB.

The function then begins the first of two loops.
    WHILE l_offset < DBMS_LOB.GETLENGTH(p_clob) LOOP
      l_segment := DBMS_LOB.SUBSTR(p_clob,32767,l_offset);
The outer loop splits the main CLOB into segments of 32K, so that the inner loop can process the data.

The inner loop then checks for the search string, p_what, in the segment of CLOB that is current being processed.
        l_pos := DBMS_LOB.INSTR(l_segment,p_what,l_pos+c_withLen);
        EXIT WHEN (NVL(l_pos,0) = 0) OR (l_pos = 32767-c_withLen);
If the search string is not found it exits the loop to process the next segment. If the search string is found at the very end of the string, it moves on to the next segment to be processed, as the segments overlap by the length of the search string, so a search string occurring over a segment break does not get missed.
        l_segment := TO_CLOB( DBMS_LOB.SUBSTR(l_segment,l_pos-1)
                            ||p_with
                            ||DBMS_LOB.SUBSTR(l_segment,32767-c_whatLen-l_pos-c_whatLen+1,l_pos+c_whatLen));
The segment is then rebuilt, from the data before the search string, the replacement string, then the data after the search string.
      l_return := l_return||l_segment;
      l_offset := l_offset + 32767 - c_whatLen;
The processed segment is then added to the return CLOB and the offset for the segment is increased by 32K minus the search string length.

Further Reading


AskTom: Tom creates a procedure, which uses the DBMS_LOB.WRITE procedure to perform a replace on a CLOB.
Oracle: Oracle documentation on the DBMS_LOB package

HunBug Store








Comments are all individually read, none are automatically posted to the site.