Originally posted by abutler
Still need help on how to use DBMS_LOB specifically for updating text in an existing clob column.

Anyone help?????

Thanks, Ali
OK, ok :-)

Here is what you do.
Assume that you have a table called lob_taulu:

Code:
SQL> desc lob_taulu
 Name                            Null?    Type
 ------------------------------- -------- ----------------------
 KEY                                      NUMBER
 C                                        CLOB

SQL> 

SQL> insert into lob_taulu values(1,'The football team Liverpool are known as the Red Devils');

1 row created.

SQL> commit work;

Commit complete.

SQL>
Now, we notice that we made a mistake. Liverpoool should be replaced with Manchester United. In other words, we need to make an update on a CLOB field.

I run the following block:

Code:
SQL> declare
  2  lob_loc clob;
  3  begin
  4  select c into lob_loc from lob_taulu where key = 1 for update;
  5  search_n_replace_clob(lob_loc,'Liverpool','Manchester United');
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL>
SQL> select C from lob_taulu;

C
----------------------------------------------------------------
The football team Manchester United are known as the Red Devils
Here search_n_replace_clob is the following procedure thanks to Metalink:

Code:
PROCEDURE search_n_replace_clob (lob_loc IN OUT CLOB,
                                 search_str VARCHAR2,
                                 replace_str VARCHAR2)
AS
temp_clob CLOB;
end_offset INTEGER := 1;
start_offset INTEGER := 1;
occurence NUMBER := 1;
replace_str_len NUMBER := LENGTH(replace_str);
temp_clob_len NUMBER := 0;
lob_loc_len NUMBER := 0;
BEGIN
DBMS_LOB.CREATETEMPORARY(temp_clob,TRUE,DBMS_LOB.SESSION);
LOOP
end_offset := DBMS_LOB.INSTR(lob_loc,search_str,1,occurence);
IF end_offset = 0 THEN
temp_clob_len := DBMS_LOB.GETLENGTH(temp_clob);
lob_loc_len := DBMS_LOB.GETLENGTH(lob_loc) - start_offset + 1;
IF lob_loc_len > 0 THEN
DBMS_LOB.COPY(temp_clob,lob_loc,lob_loc_len,temp_clob_len+1,start_offset);
END IF;
EXIT;
END IF;
temp_clob_len := DBMS_LOB.GETLENGTH(temp_clob);
IF (end_offset - start_offset) > 0 THEN
DBMS_LOB.COPY(temp_clob,lob_loc,(end_offset - start_offset),temp_clob_len+1,start_offset);
END IF;
start_offset := end_offset + LENGTH(search_str);
occurence := occurence + 1;
IF replace_str IS NOT NULL THEN
DBMS_LOB.WRITEAPPEND(temp_clob,replace_str_len,replace_str);
END IF;
END LOOP;
IF LENGTH(search_str) > LENGTH(replace_str) THEN
DBMS_LOB.TRIM(lob_loc,DBMS_LOB.GETLENGTH(temp_clob));
END IF;
DBMS_LOB.COPY(lob_loc,temp_clob,DBMS_LOB.GETLENGTH(temp_clob),1,1);
END ;
Is it clear?