Replace function for blob data types in Oracle

Back to Menu

This article would be very helpful to you if you are looking a way to replace some word from a large TEXT store in blob field in Oracle.



By the way if you intent to store TEXT only better use CLOB/NCLOB columns instead of BLOB.

Replace function in Oracle accepts three parameters 'input_string, search_string and replacement_string' and

both search_string and replacement_string, as well as input_string can be any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.

Because Replace function doesn't support BLOB type so first convert your data to a workable CLOB and use standard functions on the CLOB.

Other solution is to code your one REPLACE function. It would be strongly advise to change the datatype of your column. This will prevent any further character set conversion error you will likely run into in the future.

If you really want to work with blobs, then use following approach.

Create a Package with all the methods so that they can be used anywhere,anytime in you project.

Script To Create Package

set SERVEROUT ON;
CREATE OR REPLACE PACKAGE LOB_UTIL_SUPPORT  
IS  
  FUNCTION BLOBREPLACE (p_blob BLOB, p_what VARCHAR2, p_with_what VARCHAR2) RETURN BLOB; 
  FUNCTION BLOB2CLOB (p_blob BLOB) RETURN CLOB; 
  FUNCTION CLOB2BLOB (p_clob CLOB) RETURN BLOB; 
END; 
/  
CREATE OR REPLACE PACKAGE BODY LOB_UTIL_SUPPORT 
IS  
  FUNCTION BLOBREPLACE (p_blob BLOB, p_what VARCHAR2, p_with_what VARCHAR2)  RETURN BLOB 
  IS   
   BEGIN 
     RETURN CLOB2BLOB( REPLACE(BLOB2CLOB(p_blob), p_what, p_with_what) ); 
  END BLOBREPLACE; 
------------------------------ BLOB2CLOB ------------------------- 
  FUNCTION BLOB2CLOB (p_blob in BLOB) RETURN CLOB 
  IS 
   v_clob     CLOB; 
   dest_offset  INTEGER := 1; 
   src_offset   INTEGER := 1;  
   lang_context  INTEGER := DBMS_LOB.DEFAULT_LANG_CTX; 
   warning    INTEGER;  
  BEGIN 
   DBMS_LOB.CREATETEMPORARY ( v_clob, TRUE ); 
   DBMS_LOB.CONVERTTOCLOB( 
     v_clob, 
     p_blob, 
     DBMS_LOB.LOBMAXSIZE, 
     dest_offset, 
     src_offset,  
     DBMS_LOB.DEFAULT_CSID, 
     lang_context, 
     warning 
   );  
   RETURN v_clob; 
  END BLOB2CLOB; 
  ------------------------------ CLOB2BLOB ------------------------- 
  FUNCTION CLOB2BLOB (p_clob CLOB) RETURN BLOB 
  AS 
   l_blob     BLOB; 
   l_dest_offset  INTEGER := 1; 
   l_source_offset INTEGER := 1; 
   l_warning    INTEGER; 
   lang_context  INTEGER := DBMS_LOB.DEFAULT_LANG_CTX; 
  BEGIN 
   DBMS_LOB.CREATETEMPORARY(l_blob, TRUE); 
   DBMS_LOB.CONVERTTOBLOB( 
     l_blob, 
     p_clob, 
     DBMS_LOB.LOBMAXSIZE, 
     l_dest_offset, 
     l_source_offset, 
     DBMS_LOB.DEFAULT_CSID, 
     lang_context, 
     l_warning 
   ); 
   RETURN l_blob; 
  END CLOB2BLOB; 
END;  
/ 
EXIT;

Sample Code to Replace Text from BLOB Field

set serveroutput ON size 1000000;
      begin
        -- add new column
      UPDATE your_table
      SET your_column= LOB_UTIL_SUPPORT.BLOBREPLACE(your_column, 'from_string', 'to_string')
      WHERE dbms_lob.instr(your_column, utl_raw.cast_to_raw('from_string'))>0;
    commit;
end;
/
EXIT;

Back to Menu