Any Perl/DBD::Oracle users out there? ORA-1460??

From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxx>
To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
Date: Wed, 24 Mar 2010 17:30:45 -0400

Hi all,

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:  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".

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.

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?

Any thoughts or suggestions would be greatly appreciated!

Thanks,

-Mark

Any Perl/DBD::Oracle users out there? ORA-1460??

Other related posts: