Handling Clobs, upserting.

Hi all,

Can you give me some thoughts on this method of handling update/insert of CLOBS. It involves passing the clob in blocks of 32000 bytes to the add_text_to_clob procedure.

It is tuned to work with 'More updates than inserts', although it can be retuned to work for 'more inserts than updates'. This just means reordering the insert/update and using the exception DUP_VAL_ON_INDEX.

I've checked out some newsgroups, this was suggested for 8.1.7. 9i has a new function nice called merge, but that won't really work with CLOBS as you need to call the writeappend function for the update.

http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&threadm=p8efhtc2ffqc67bimv8pa4jhj3rcmno135%404ax.com&rnum=3&prev=/groups%3Fq%3Dupsert%2Boracle%26hl%3Den%26lr%3D%26ie%3DUTF-8%26selm%3Dp8efhtc2ffqc67bimv8pa4jhj3rcmno135%25404ax.com%26rnum%3D3

This article points to a concurrency issue that I may have:

http://groups.google.se/groups?hl=sv&lr=&ie=UTF-8&oe=UTF-8&threadm=5366fb41.0403240847.60a86846%40posting.google.com&rnum=1&prev=/groups%3Fq%3Dinsert%2Bupdate%2Bdbms_lock%26hl%3Dsv%26lr%3D%26ie%3DUTF-8%26oe%3DUTF-8%26scoring%3Dd%26selm%3D5366fb41.0403240847.60a86846%2540posting.google.com%26rnum%3D1

In my case, I don't think this will ever happen as only one thread tries to insert/update a record to the database at any given time.

Here's an example you can just paste into a sqlplus session:

drop table testing_clobs;

create table testing_clobs (

my_id number,

my_data clob

);

create or replace procedure add_text_to_clob( id in int, text in

varchar2 )

as

temp_clob clob;

begin

select my_data into temp_clob from TESTING_CLOBS where my_id =

add_text_to_clob.id for update;

dbms_lob.writeappend(temp_clob, length(text), text);

EXCEPTION WHEN NO_DATA_FOUND THEN insert into TESTING_CLOBS (my_id, my_data) values (id, text);

end;

/

show errors;

select my_id, dbms_lob.getlength(my_data) from TESTING_CLOBS;

exec add_text_to_clob(1, rpad('*',32000,'*'));

select my_id, dbms_lob.getlength(my_data) from TESTING_CLOBS;

exec add_text_to_clob(1, rpad('*',32000,'*'));

exec add_text_to_clob(1, rpad('*',32000,'*'));

exec add_text_to_clob(1, rpad('*',32000,'*'));

select my_id, dbms_lob.getlength(my_data) from TESTING_CLOBS;

exec add_text_to_clob(2, rpad('*',32000,'*'));

exec add_text_to_clob(2, rpad('*',32000,'*'));

exec add_text_to_clob(2, rpad('*',32000,'*'));

select my_id, dbms_lob.getlength(my_data) from TESTING_CLOBS;

Cheers,

Kevin Received on Mon Jun 21 2004 - 05:02:38 CDT