Subject: DBD::Oracle: Freeing a temporary blob

From: Philipp Lang <[email-masked]>
Date: Wed, 25 Aug 2004 16:37:57 +0200

Dear Tim,

first, I would really like to thank you for the great work you have done
with your DBI/DBD work. We are using it extensively in several projects
and we are highly pleased.

Unfortunately I have now encountered a problem we need to fix urgently,
since our customer's production is seriously  affected.

We tried for many days to find a solution (google, newsgroups,
trial-and-error...), without luck. So my last chance is to contact you
directly, although I assume you have a lot work yourself.

---

The problem:

Our database contains a stored function "ReadUnitBlob()" that returns a
temporary blob: 

function ReadUnitBlob(UnitID_IN in integer) return blob
is
...
begin
  ...
  dbms_lob.createtemporary(retBlob, false, dbms_lob.call);
  dbms_lob.loadfromfile(retBlob, bfileIn, fileLen);
  ...
  return retBlob;
end;

We call this function in Perl in a big loop to fetch a lot of blobs (~
100000) using something like:
 
my $sth = $DBH->prepare("select ReadUnitBlob(:unitID) from dual", {
ora_auto_lob => 0 });
$sth->bind_param(":unitID", $unitID);
$sth->execute();
my ($loc) = $sth->fetchrow_array();
$sth->finish();
...
while(my $data = $DBH->ora_lob_read($loc, $offset, $chunk_size))
  ...

Although this works, it has a big disadvantage: Oracle does not
automaticaly free the temporary blob, so it shortly runs out of temp.
space. (confirmed with "select * from v$temporary_lobs").

Acording to the Oracle docs this can be we solved by implicitly freeing
the temp. blob, e.g. by calling the PLSQL method
"dbms_lob.freetemporary()". I tried differnt ways to do this with
DBD::Oracle, but never succeeded. I just did not manage to fetch the LOB
locator from the my stored procedure and to pass it to a call of
"dbms_lob.freetemporary()":

my $sth = $dbh->prepare("begin :bloc := ReadUnitBlob(:unitID); end;", {
ora_auto_lob => 0 });
my $bloc;
$sth->bind_param_inout( ":bloc", \$bloc, ORA_BLOB);
$sth->bind_param(":unitID", $unitID);
$sth->execute();

obviously the "ORA_BLOB" type for bind_param_inout() is wrong here ;)

Can you help me??? Do you know a way how to free the temp. blob using
DBD::Oracle (using Oracle::OCI is not possible, since the customer is
strinctly refusing to install it).

Otherwise, would it be possible include an additional DBD::Oracle LOB
Locator Method "ora_lob_freetemporary()" that warps
"OCILobFreeTemporary" ?

Any help is really appreciated!

Greeting from Germany,

  Philipp

--
Dipl. Inf. Philipp Lang                         Genericom Software GmbH
Tel. +49 (0)7031/8739-26                              Tilsiter Str. 4-6
Fax  +49 (0)7031/8739-11                             71065 Sindelfingen
[email-masked]                                        www.genericom.de

---

From: Tim Bunce <[email-masked]>
Date: Wed, 25 Aug 2004 21:54:52 +0100

> and we are highly pleased.

Thanks Philipp!

> directly, although I assume you have a lot work yourself.

I am very busy and what little spare time I have for DBD::Oracle
is going into trying to get 1.16 released (which is proving to be
hard as we're tripping over Oracle bugs when an Oracle 8 client
talks to an Oracle 9+ server.) It's a major release that will help
many people so I need to give that priority.

Separately I am exploring ways to fund DBI and DBD::Oracle development
so I can devote more time to it. There's is much that needs to be
done and much more that could be done beyond that.

The Perl Foundation (TPF) have setup a way for people and companies
to make donations for DBI development [as yet unannounced so please
keep to yourself]. A note on the contribution could indicate that the
donor would like it used for a particular purpose, such as DBD::Oracle.

Anyway, to cut a long story short, it would be much easier to devote
time to this if it could be funded in some way.

I think the TPF setup is working so it could be done that way.
Alternatively you could contract me to do the work for you.
That would be quicker and simpler for you as TPF's non-profit
status doesn't make a difference to European donors.

My standard daily rate for add-hoc consulting is 1600 Euro.
I'd expect to be able to sort this out inside a day, and prefer
fixed-price quotes anyway, so would 1600 Euro be okay?

Of course, if you feel your company would like to either
make a general contribution to DBI/DBD::Oracle development beyond
that, or to fund the development of specific functionality that
would be of extra value to you then I'd be happy to talk about that.

I hate asking for money, and would much rather dig into the code and
reply with a patch, but it's just not practical for me now. Sorry.

Tim.

---

From: Philipp Lang <[email-masked]>
Date: Thu, 26 Aug 2004 12:30:47 +0200

> reply with a patch, but it's just not practical for me now. Sorry.

Thanks for your quick reply. I can fully understand your position in
this issue. We will need to submit this issue to our customer, and
eventually it will be their decision, since a) the problem technically
originates in external components
and b) our work is payed on a time and material basis.

Thanks again,
  Philipp

---

From: Tim Bunce <[email-masked]>
Date: Thu, 26 Aug 2004 15:18:55 +0100

Okay. Thanks. Let me know how it turns out.

Tim.