Updating Clob Field

Can anyone tell me how to update/alter some text that is held in a clob column? I can display the text but when I edit it in Toad I get the error ORA-01036 illegal variable name/number - what does that mean and how do I get round it?

Thanks.

By using DBMS_LOB. Send me an email if you do not know how to use DBMS_LOB.

I'm sorry no I don't know how to use DBMS_LOB - can you advise?

Thanks, Ali

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

Anyone help?????

Thanks, Ali

Quote:

---

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?

Thanks Julian that worked a treat!

Not very good with pl/sql yet so it does help when I get pointed in the right direction, although understanding what it was doing will take me a little longer to work out!!!


Thanks again.

Ali