More on closing Oracle ref cursors
- From: michael@xxxxxxxxx (Michael Styer)
- Date: Wed, 20 Apr 2005 13:40:28 -0400
Hi. I'm working on a project using DBD::Oracle 1.16, DBI 1.43, Perl 5.6,
WinXP, and Oracle 8.1.7.4.
My script runs through a loop many times, and each time through it calls
two PL/SQL stored procs via a wrapper class I've built. At some point in
the loop I run out of ref cursors; Oracle returns a "maximum cursors
exceeded" error. Obviously the cursors that get opened and returned by
the stored procs aren't getting closed by Perl for some reason.
I was under the impression from previous postings on this list that
Oracle's ref cursors get closed automatically when the Perl variable
holding them goes out of scope. As far as I can see, the variable
holding the ref cursor goes out of scope at the end of each loop. But in
case Perl doesn't close the cursors automatically (there did seem to be
some debate on the issue) I'm trying and failing to close them
explicitly myself.
I've included the relevant bits of my code below -- if you have any
advice or experience on the issue, I'd love some pointers.
Thanks very much,
Mike
==== DETAILS ====
The way I've built my system, I have a wrapper class that stores a DBI
connection object in $self->dbh. Here's the constructor:
sub new {
my $class = shift;
my %args = (%DEFAULTS, @_);
DBI->trace_msg("Connecting to database\n");
my $dbh = DBI->connect( "dbi:Oracle:$DBNAME",
$DBUSER, $DBPASS, \%args )
or die "Unable to connect to $DBNAME: $DBI::errstr\n";
DBI->trace_msg("Storing database handle ($dbh)\n");
my $self = { dbh => $dbh };
bless $self, $class;
return $self;
}
Then I have a generic method that calls stored procs via the AUTOLOAD
method. Here's AUTOLOAD:
sub AUTOLOAD {
my $self = shift;
die "Not an object\n" unless ref $self;
my $name = our $AUTOLOAD;
$name =~ s/^.*::([^:]+)$/\1/;
return if $name eq 'DESTROY';
if ($name =~ /^(Get|Create|Delete|Add|Update)/ ) {
return $self->_do_proc(uc $1, $name, @_);
} elsif (exists $self->{$name}) {
if (@_) {
return $self->{$name} = shift;
} else {
return $self->{$name};
}
} else {
warn "Attempt to access non-existent method/property: $name\n";
}
}
And here's my generic _do_proc method:
sub _do_proc {
my $self = shift;
my ($type, $proc, @args) = @_;
unless ($type =~ /^(GET|CREATE|DELETE|ADD|UPDATE)$/) {
die "Unknown type ($type) passed to _do_proc!\n";
}
my $inout = 0;
$inout++ if $type =~ /^(GET|CREATE)$/;
my @params = ();
for (my $i = 0; $i < @args; $i++) {
push @params, ":a$i";
}
push @params, ":out" if $inout;
my $arg_str = join ', ', @params;
my $sql = __unindent(<<" SQLEND");
~BEGIN
~ $PKGNAME.$proc($arg_str);
~END;
SQLEND
my $sth = $self->dbh->prepare($sql);
#print "Prepared statement \n".$sth->{'Statement'}."\n";
my $out;
for (my $i = 0; $i < @args; $i++) {
$sth->bind_param($params[$i], $args[$i]);
}
if ($type eq 'GET') {
$sth->bind_param_inout(':out', \$out, 0, { ora_type => ORA_RSET
} );
} elsif ($type eq 'CREATE') {
$sth->bind_param_inout(':out', \$out, 10);
} else {
$out = 1;
}
my $ok = $sth->execute;
if ($ok) {
#print "Successfully executed statement\n";
} else {
return undef;
}
return $out;
}
Then in order to close the cursor returned from _do_proc I have a
CloseCursor method:
sub CloseCursor {
my ($self, $cursor) = @_;
unless (defined $cursor) {
warn "No cursor passed to CloseCursor!\n";
return 0;
}
my $sql = __unindent(<<" SQLEND");
~BEGIN
~ close :cur;
~END;
SQLEND
my $sth = $self->dbh->prepare($sql);
print $sth->{'Statement'};
## Method 1
$sth->bind_param_inout(':cur', \$cursor, 0, { ora_type => ORA_RSET }
);
## Method 2
#$sth->bind_param(':cur', $cursor, { ora_type => ORA_RSET } );
## Method 3
#my $ok = $self->dbh->do("BEGIN CLOSE ?; END;", undef, $cursor);
if ($sth->execute) { ## For Methods 1 and 2
#if ($ok) { ## Part of Method 3
return 1;
} else {
die "Error closing cursor: ".$self->dbh->errstr."\n";
return 0;
}
}
You can see the different methods there I've tried closing the cursor.
So my script basically looks like this:
my $sth = $db->GetSomething($input);
if ($db->err) { print $db->errstr; exit; }
my $data = $sth->fetchrow_hashref;
$sth->finish;
$db->CloseCursor($sth);
The error I get from Method 1 is:
ORA-01023: Cursor context not found (Invalid cursor number) (DBD: odescr
failed)
>From Method 2 I get:
ORA-01008: not all variables bound (DBD: oexec error)
And from Method 3 I get:
panic: dbd_rebind_ph alen 23 > maxlen 0 (incnul 0) at
C:/Perl/site/lib/DBI.pm line 1428.
I think I'm doing everything right; I've found all three methods from
different parts of the DBI and DBD::Oracle documentation. But obviously
something isn't working.
So if you have any suggestions as to how to make this work, I'd really
appreciate it!
Thanks.
.
- Prev by Date: Re: Explain Something to me..........
- Next by Date: RE: More on closing Oracle ref cursors
- Previous by thread: MySQL - Using Bind Params, Error Logic
- Next by thread: RE: More on closing Oracle ref cursors
- Index(es):
Relevant Pages
|
