Re: Closing Oracle cursors

[RazorbladeBidet]

How are you opening these cursors? Are they explicitly opened inside of function/procedure calls or are you opening and closing them in SQL passed to DBI?

If the former, make sure the functions and procedures you call properly close their own cursors. If the latter, make sure you close your own cursors.

I'm currently running many large PL/SQL programs (with many cursors) on AIX running Oracle 9i and have not encountered any max cursor problems. What platform and version of Oracle are you running?

[mickey]

I'm working on Windows XP, with Oracle 8.1.7.4.0. The cursors are opened in PL/SQL and returned to DBI via the following wrapper function (which is called by AUTOLOAD):

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_RSE
+T } );
    } 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;
    }

    # TODO: Check for statement errors!
    
    return $out;
}
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"
+;
    }
    
}

In the particular instance where the error is occurring, I'm calling the above function, as $db->GetCurveByIdentifier, like this:

my $bb_curve_sth = $db->GetCurveByIdentifier($bb_code);
if ($db->err) {
    die "ERROR: ".$db->errstr."\n";
}
my $bb_curve = $bb_curve_sth->fetchrow_hashref;
$bb_curve_sth->finish;
$db->CloseCursor($bb_curve_sth); ## Close the cursor

And there CloseCursor prints the error noted before.

What do you think?

[Transient]

Have you tried not explicitly finish'ing the cursor?
Also, there is an example in curref.pl under the Oracle.ex directory in the DBD::Oracle distribution. Does that work for you?

[mickey]

Yep, I've tried not calling ->finish ... I get the same error.

As for the curref.pl example, I haven't actually run it -- I don't have the right sample tables, or the authority to create packages.

But the example defines this procedure:

    PROCEDURE ref_cursor_close (curref IN cursor_ref)
    IS
    BEGIN
      close curref;
    END;

and calls it like this:

$sql = qq(
   BEGIN
     curref_test.ref_cursor_close(:curref);
   END;
);
$sth = $dbh->prepare($sql);
$sth->bind_param(":curref", $curref, {ora_type => ORA_RSET});
$sth->execute;

which, as far as I can see, is exactly what I'm doing. NB: I've tried using both bind_param and bind_param_inout; the example uses bind_param, but the DBD::Oracle docs use bind_param_inout. Same error in both cases.

Can you see anything that's going on there that is effectively different in my code?

Thanks for your suggestions... :)