(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.
Best Answer
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:
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.