Re: Copy blob portion to another blob

Vladimir M. Zakharychev (bob_at_dpsp-yes.com) wrote:

> Dunno for your original question - there may be a lot of reasons
> why dbms_lob.copy works slowly, starting with poorly tuned
> database with heavy I/O contention and ending with some bug
> in the routine - you didn't specify the exact version of Oracle you
> are using so we're guessing here.

I am using 8.05 and 8.1

The procedure is:

create or replace procedure TransferBlobData(dstID in int, dstOffset in int, srcID in int, srcOffset in int, amount in int) is src blob;
dst blob;
begin
  select blobData into src from BlobTable where id = srcID;   select blobData into dst from BlobTable where id = dstID for update;   dbms_lob.copy(dst, src, amount, dstOffset, srcOffset);   commit;
end TransferBlobData;

BlobTable is defined as:
blobData - blob
id - int

It works 5-10 times slower than downloading blob to the client, trimming it there and uploading it back to the server.

> As of temporary LOBs, they were introduced in 8.1. The only way
> I can think of for implementing similar (but not exactly)
> functionality is to use global temporary table with LOB column and
> ON COMMIT DELETE ROWS, insert an EMPTY_xLOB() into it and
> immediately select it back FOR UPDATE, then do whatever you
> please with it, finally copy it to some permanent LOB and COMMIT
> to dispose the temporary copy. This would roughly emulate
> a temporaty LOB with dbms_lob.call duration. Roughly, because it
> will actually emulate inexistent 'transaction' duration. If you need
> dbms_lob.session duration, use temporary table with ON COMMIT
> PRESERVE ROWS, this will keep your data in the table for duration
> of your session.

Here is what I tried first:

create or replace function ReadBlobData(key in int, offset in int, amount in int) return blob is
src blob;
dst blob;
begin
  dbms_lob.createtemporary(dst, TRUE, dbms_lob.call);   select blobData into src from ShBlobData where id = key;   dbms_lob.copy(dst, src, amount, 1, offset);   return dst;
end ReadBlobData;

Obviously, it doesn't work under Oracle 8.05 (there is no createtemporary). I tried to change it to:

create or replace function ReadBlobData(key in int, offset in int, amount in int, tempBlobID in int) return blob is
src blob;
dst blob;
begin
  select blobData into dst from ShBlobData where id = tempBlobID for update;   select blobData into src from ShBlobData where id = key;   dbms_lob.copy(dst, src, amount, 1, offset);   return dst;
end ReadBlobData;

But now I am getting (quite reasonable) "ORA-06571: Function READBLOBDATA does not guarantee not to update database" error.

--
Mike Kluev.
PS. Remove "-delete-." part of my e-mail address to reply.