Code Sample: Replace String (varchar) text in a CLOB

From: Roch <roch.daneau_at_xwave.com>
Date: 20 May 2004 12:51:08 -0700
Message-ID: <eb427a78.0405201151.17072559@posting.google.com>

Spent alot of time trying to find some sample code to replace all occurrences of some text with new text for CLOB variables in PLSQL. Didnt find any, but noticed a few other posts looking for similiar code... so i broke down and wrote one. Here it is for anyone interested:

• FUNCTION replaceClob
  --
  
• This function replaces all occurences of a string with another for a clob.
  --
  
• Input parameters:
  --
  
• 1) clob src - the CLOB source to be replaced.
• 2) replace str - the string to be replaced.
• 3) replace with - the replacement string.

   FUNCTION replaceClob

     ( srcClob IN CLOB,
       replaceStr IN varchar2,
       replaceWith IN varchar2 )

   RETURN CLOB
   IS    

     l_buffer VARCHAR2 (32767);
     l_amount BINARY_INTEGER := 32767;
     l_pos INTEGER := 1;
     l_clob_len INTEGER;
	 newClob clob := EMPTY_CLOB;
	  

   BEGIN

• initalize the new clob dbms_lob.CreateTemporary( newClob, TRUE );

	 l_clob_len := DBMS_LOB.getlength (srcClob);
     WHILE l_pos < l_clob_len
     LOOP
	 
         DBMS_LOB.READ (srcClob,l_amount,l_pos,l_buffer);
         IF l_buffer IS NOT NULL
         THEN
		   -- replace the text
		   l_buffer := replace(l_buffer,replaceStr,replaceWith);
		   -- write it to the new clob
	       DBMS_LOB.writeAppend(newClob, LENGTH(l_buffer), l_buffer);
         END IF;
         l_pos :=   l_pos + l_amount;
		 
     END LOOP;
	 
	 RETURN newClob;
	 

   EXCEPTION
   WHEN OTHERS THEN
        RAISE;
   END;

• Roch D.

Received on Thu May 20 2004 - 14:51:08 CDT