Search This Blog

Wednesday, 28 September 2011

search and replace in an Oracle CLOB

Here comes a very useful function which replaces a string in a CLOB with another string the function has been taken out from
--      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
       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;
         --It first checks that the p_what string is not null, to save time checking the CLOB.
         IF p_what IS NOT NULL THEN
           --The function then begins the first of two loops. The outer loop splits the main CLOB into segments of 32K, so that the inner loop can process the data.
           WHILE l_offset < DBMS_LOB.GETLENGTH(p_clob) LOOP
             l_segment := DBMS_LOB.SUBSTR(p_clob,32767,l_offset);
             LOOP --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)
             END LOOP;
             --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.
           END LOOP;
         END IF;

No comments:

Post a Comment