0

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 " ".

1

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;
  • Thanks for to answer I have not had a chance to test it. I appreciate your help. – Melara Nov 8 '12 at 0:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.