-
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.
Oracle Certified Master
Oracle Certified Professional 6i,8i,9i,10g,11g,12c
email: ocp_9i@yahoo.com
-
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
-
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?
Oracle Certified Master
Oracle Certified Professional 6i,8i,9i,10g,11g,12c
email: ocp_9i@yahoo.com
-
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|