Massive update to Clob fields oracle 10g

oracle-10gplsql

I need to remove a string of characters from 2000+ clobs fields in oracle 10g. How would I do this in PL/SQL?

For example, in table t1 there is a clob field called VBase that needs to have the string ".magic()" replaced with " ".

Best Answer

Something like this for each field.

--Setup.
DROP TABLE t1;
CREATE TABLE t1 (ID Number(4), VBase Clob);
INSERT INTO t1 (SELECT level, '*' || '.magic()' || '*' FROM dual 
   connect by level <=2001);
COMMIT;
SELECT * FROM t1;

--Replace.
DECLARE
   cBatchSize CONSTANT Number(3) := 100;
   Cursor vQuery Is SELECT ID, VBase FROM t1 FOR UPDATE;   
   Type tTable Is Table Of t1%rowtype;
   vData tTable;
   vNew Clob;
   vLocation Number(10);
   vLobLength Number(10);

   Procedure LobReplace(ioLob In Out Clob, iOld In Varchar2, iNew In Varchar2 )
   As
       vPosition number;
   Begin
       vPosition := dbms_lob.instr(ioLob, iOld);
       If (nvl(vPosition,0) > 0) Then
           DBMS_Lob.Copy( ioLob, ioLob, dbms_lob.getlength(ioLob)
              , vPosition+length(iNew), vPosition+length(iOld) );
           DBMS_Lob.Write( ioLob, length(iNew), vPosition, iNew );
           If (Length(iOld) > Length(iNew)) Then
               DBMS_Lob.Trim(ioLob
                  , dbms_lob.getlength(ioLob) - (length(iOld)-length(iNew)));
           End If;
       End If;
   End;


BEGIN
   Open vQuery;
   Loop
      Fetch vQuery Bulk Collect Into vData Limit cBatchSize;
      For i In 1..vData.Count() Loop
         LobReplace(vData(i).VBase,'.magic()',' ');
      End Loop;
      ForAll i In 1..vData.Count
         UPDATE t1 SET vBase = vData(i).VBase WHERE ID = vData(i).Id;
      Exit When vData.Count() < cBatchsize;
   End Loop;
   Close vQuery;

   COMMIT;
END;
/

--Verify.
SELECT * FROM t1;
Related Question