Martin J. Evans wrote:
> 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?
BTW, forgot to mention that I used the following SQL to locate the SQL
that created the temporary lobs. It returns far less rows that you'd
expect from CACHE_LOBS (above):
select b.SQL_TEXT, a.USERNAME, a.SQL_ID, a.CONTENTS, a.SEGTYPE,
a.SEGFILE#, a.SEGBLK#, a.EXTENTS, a.BLOCKS from V$TEMPSEG_USAGE a
join v$sql b on a.sql_id = b.sql_id
I've not found a good definition of v$temporay_lobs yet.
>> 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
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com
