How to avoid error maximum open_cursor exceeded when using Class::DBI

Question

(Update to answer Jonathan Leffler's question below):

We're running Perl 5.8.7 and Oracle 11.1.0.7.0.

Due to the company's policy, developers have no arbitrary control in regard to software upgrade. Giving the proposal to the upper management takes months to be followed up (if approved) - I guess it's not a surprisingly odd situation for several other companies too.

I inherited the program from someone else left the company and found the warning about "issuing rollback() ..." from the application log file. The actual problem "maximum open_cursor exceeded" was found after I run *DBI_TRACE=2=/tmp/trace.log program_name.pl*.

Looking at the number of $dbh->{ActiveKids}, $dbh->{Kids}, and $dbh->{CachedKids}, I assume the maximum open cursor is 50 as the error happens after it reaches 50.

---

Our legacy production codes are using these modules:

• DBI - 1.48
• Ima::DBI - 0.33
• Class::DBI - 0.96
• Class::DBI::Oracle - 0.51
• DBD::Oracle - 1.16

For some odd policy reason, upgrading the module to a newer version is not possible :(

The application relies on using CDBI to handle relationships on a large number of tables. A simplify snippet of the code is as below:

JOB:
foreach my $job (@jobs) {
  my @records = $job->record; 

  RECORD: 
  foreach my $record (@records) {
      my @datas = $record->data;

      DATA:
      foreach my $data (@datas) {
          ....
      }
  }
}

where each @jobs, $record, and $data is an object to a table and the inner most loop calls several other triggers.

Somewhere after several loops I'm getting an Oracle error: *maximum open_cursor exceeded* and then I got the error from the CDBI: issuing rollback() for database handle being DESTROYE'd without explicit disconnect.

I can workaround it by undef-ing the DBI CachedKids on the most outer loop, with:

# somewhere during initialization
$self->{_this_dbh} = __PACKAGE__->db_Main();

....

JOB:
foreach my $job (@jobs) {
    RECORD: ....
        DATA: ....

    $self->{_this_dbh}->{CachedKids} = undef;
}

Is that the proper way to do it?

Or does CDBI support a way to clear statement handle the same way as DBI $sth->finish() ?

Thanks.

Answer 1

At some point, you will have to explain why you cannot upgrade to more nearly current versions of the software. You didn't mention which version of Perl you are using, or which version of Oracle; somehow, I suspect that it is neither 5.10.1 nor 11gR2.

Current versions:

• Class::DBI 3.0.17
• Class::DBI::Oracle 0.51
• DBI 1.609 (version 1.48 is from 2005)
• DBD::Oracle 1.23 (version 1.16 is from 2004)
• Ima::DBI 0.35

What changed recently? Why are you suddenly finding problems in a piece of software that was, presumably, very stable? Is this new code?

With plain DBI, when you undef a statement handle (by having it go out of scope, for example), then the resources associated with it are released - more or less noisily. However, there is enough infrastructure between Class::DBI and DBI that it is hard to tell how this might map.

• Have you worked out what the limit on open cursors actually is?
• Have you worked out whether you've opened enough cursors to actually exceed that limit?
• Have you tried running with DBI_TRACE set in the environment? A value such as 3 will tell you a fair amount about what it going on - maybe too much. It would show whether cursors are being released properly or not.
• Have you tried reducing the number of tables manipulated in a single session?
• Have you considered disconnecting and reconnecting between manipulating tables?
• Is there a way to get to the statement handle corresponding to the Class::DBI abstractions, so that you can in fact execute $sth->finish()?