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