Bobak, Mark wrote:
> Hi all,
>
> I'm new to the list, and I'm a DBA, not really a Perl programmer, but I'm
> running into this issue that I hope someone can help with.
>
> I think I'm getting dangerously close to getting this to work. But, I'm just
> not quite getting there.
>
> We have an application coded in Perl w/ DBI * DBD::Oracle. Versions:
> Database: Oracle 11.2.0.1.0, Perl 5.10.1, DBI 1.609, DBD::Oracle 1.23.
>
> We've had a long standing problem of leaking temporary LOBs when handling
> lobs. (i.e., count of lobs reported in V$TEMPORARY_LOBS continues to
> increase till the session disconnects from the database.) I recently
> discovered that if you use ORA_CLOB and bind by reference (bind_param_inout)
> it solves the leak problem. But my problem now is, prepare works, bind
> works, but execute hits ORA-1460 "unimplemented or unreasonable conversion
> requested".
I am also seeing leaks in DBD::Oracle in some daemons which run
continuously but I am still in the middle of tracking them down.
> So, my questions are:
> If I'm using temporary lobs that are created/managed in Perl, I should use:
> $sth->bind_param_inout(':pi_cit_comprep', \$paramHash->{cit_comprep},
> {ora_type => ORA_CLOB});
>
> to do the bind? I think this is correct, as the LOB leaking goes away, but,
> doing it this way, I hit ORA-1460 on CLOBs larger than 32k.
bind_param_inout is for passing a lob in and getting one back or just
getting a lob back. bind_param is for passing a lob in only. Which are
you really doing? I am guessing you are passing a lob in as you also say
later you used bind_param.
If you execute fails with bind_param_inout then I'd guess the leak goes
away because DBD::Oracle doesn't do something that causes a leak because
of the failure.
> Previously, I was binding with:
> $sth->bind_param(':pi_cit_comprep', $paramHash->{cit_comprep}, {ora_type =>
> ORA_CLOB});
>
> And this never hit the ORA-1460 error, but would leak temporary LOBs.
>
> So, how do I *both* avoid leaking temporary LOBs *and* not encounter ORA-1460
> on LOBs larger than 32k? Is this a bug? In Oracle? Perl? DBI?
> DBD::Oracle?
Fix the leak ;-)
> Any thoughts or suggestions would be greatly appreciated!
>
> Thanks,
>
> -Mark
What is the type of the column you are inserting into - LONG or LONG RAW
by any chance?
Are you attempting to do the insert in a procedure?
How large is the data you are passing - more than 32K?
I'm not sure you can insert more than 32K into a LONG in a procedure.
Have you read "Binding for Updates and Inserts for CLOBs and BLOBs" in
the DBD::Oracle pod.
I'm way too busy at the moment to look into the leak I am seeing but I
will get around to looking into it more eventually.
Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com
