n  isopen:  This function checks to see if the LOB was already opened using the input locator.

n  createtemporary:  The procedure createtemporary creates a temporary CLOB or BLOB and its corresponding index in the user default temporary tablespace.

n  instr:  Used to return the matching position of the nth occurrence of the pattern in the LOB.

n  getlength:  Used to get the length of specified LOB.

n  copy: Copies part or all of a source internal LOB to a destination internal LOB.

n  writeappend:  Writes a specified amount of data to the end of an internal LOB.

n  trim:  Trims the value of the internal LOB to the length specified by the newlen parameter.

Here is how to perform, search and replace in a CLOB with some procedures of the dbms_lob package. First, create the test table and insert some rows.

Conn pkg/pkg#123

Connected to Oracle 11g Enterprise Edition Release 11.1.0.6.0

Connected as pkg

drop table

   tab_dbms_lob_search

purge

/

Table dropped

create table tab_dbms_lob_search(

   clob_id number,

   c clob

);

Table created

insert into

   tab_dbms_lob_search

values

   1,'Oracle Database 7i,8i,9i and 10g')

/

1 row inserted

insert into

   tab_dbms_lob_search

values

   (2,'Oracle Database 7i,8i,9i and 10g')

/

1 row inserted

commit

/

Commit complete

Let's create a procedure that uses the dbms_lob package to search and replace text within a LOB (CLOB, BLOB).

create or replace procedure
   proc_dbms_lob_search_rep(

lob_local   in out clob,

        srch_string varchar2,

        rep_string  varchar2) as

temp_clob      clob;

end_offset     integer := 1;

start_offset   integer := 1;

nth      number := 1;

rep_string_len number := length(rep_string);

temp_clob_len  number := 0;

lob_local_len  number := 0;

begin

     if lob_local is NULL then

       raise_application_error(-20001, 'LOB is empty. You need to execute inserts first!');

     end if;

     --The function isopen check to see if the LOB was already opened using the input locator

     if dbms_lob.isopen(

           lob_local) = 0

     dbms_output.put_line(' LOB is open!');

     then

       null;

     dbms_output.put_line(' LOB is closed!');

     end if;

     --The procedure create temporary creates a temporary CLOB

     dbms_lob.createtemporary(

        temp_clob,

        TRUE,

        dbms_lob.session);

LOOP

       -- The function instr returns the matching position of the nth     occurrence of

       --the pattern in the LOB, starting from the offset you specify.

       end_offset := dbms_lob.instr(lob_local, srch_string, 1, nth);

       if end_offset = 0

   then

         temp_clob_len := dbms_lob.getlength(temp_clob);

         lob_local_len := dbms_lob.getlength(lob_local) - start_offset + 1;

         if lob_local_len > 0

         then

         --The procedure copy copies all, or a part of, a source internal LOB to a destination internal LOB.

           dbms_lob.copy(

              temp_clob,

              lob_local,

              lob_local_len,

              temp_clob_len + 1,

              start_offset);

         end if;

         exit;

       end if;

       --The function getlength gets the length of the specified LOB.

       temp_clob_len := dbms_lob.getlength(temp_clob);

       if (end_offset - start_offset) > 0

       then

         dbms_lob.copy(

            temp_clob,

            lob_local,

            (end_offset - start_offset),

            temp_clob_len + 1,

            start_offset);

       end if;

       start_offset := end_offset + length(srch_string);

       nth    := nth + 1;

       if rep_string is not null then

   --The procedure writeappend writes a specified amount of data to the end of an internal LOB.

         dbms_lob.writeappend(

            temp_clob,

            rep_string_len,

            rep_string);

       end if;

     end loop;

     if length(srch_string) > length(rep_string) then

       --The procedure trim trims the value of the internal LOB to the length you specify in the

       --newlen parameter

       dbms_lob.trim(

          lob_loc => lob_local,

          newlen  =>  dbms_lob.getlength(

                        temp_clob)

       );

     end if;

     dbms_lob.copy(

        lob_local,

        temp_clob,

        dbms_lob.getlength(temp_clob), 1, 1);

   end;

  /

Procedure created

--Check for errors

show errors

No errors for procedure pkg.proc_dbms_lob_search_rep

This procedure executed the following steps: 

1.       First, it finds the position of the first occurrence of the searched text.

2.       Then it creates a temporary NCLOB, copies everything from the original NCLOB to it up to the point where it found the text to be replaced, and appends the new text.

3.       Now, it trims the temporary NCLOB to keep only what came after the word to replace.

Now let's look at the table results to see how they are before the search and replace process.

select

   *

from

   tab_dbms_lob_search;

   CLOB_ID C

---------- ---------------------------------------------------------

         1 Oracle Database 7i,8i,9i and 10g

         2 Oracle Database 7i,8i,9i and 10g

Call the procedure to change the value for the first row only (clob_id=1).

declare

      lob_local clob;

    begin

      select c

         into lob_local

      from

tab_dbms_lob_search

      where

clob_id = 1

      for update;

      proc_dbms_lob_search_rep(

         lob_local   => lob_local,

         srch_string =>  ' and 10g',

         rep_string  =>  ' ,10g and 11g ');

     commit;

   end;

 /

The results below show the new replaced text.

select

   *

from

   tab_dbms_lob_search

/

CLOB_ID   C

------ ----------------------------------------------------------

         1 Oracle Database 7i,8i,9i ,10g and 11g

         2 Oracle Database 7i,8i,9i and 10g

In the preceding example, the following procedures and functions were used:  isopen, createtemporary, instr, getlength, copy and trim.