Re: Handling Clobs, upserting.

Forgot to mention:

Oracle Version: 9.2.0.5.0

I'm doing this because I can't use CLOBs on my application side. I have to pass strings.

"Kevin Crosbie" <caoimhinocrosbai_at_yahoo.com> wrote in message news:548d379bcb7d8589b98666a9646939b5_at_news.teranews.com...
> 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 - 10:53:05 CDT