Re: Help with handling CLOBs in Oracle and ORA-1460 error

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

Were you looking at CACHE_LOBS?

As I said in my previous reply we have a leak with DBD::Oracle which
I've never been able to track down and we use a lot of lobs.

My daemon process currently has over 400 CACHE_LOBS (after 10 minutes of
running) and it is steadily increasing.

Do you know what CACHE_LOBS are and are you sure this is a sign of
leaking temporary lobs?

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

Martin
-- 
Martin J. Evans
Easysoft Limited
http://www.easysoft.com