mickey has asked for the wisdom of the Perl Monks concerning the following question:

I'm getting a "maximum cursors exceeded" error using DBD::Oracle, and to make sure my cursors are closed I'm trying to close them directly myself.

I've written an object wrapper around the DBI object to allow me to call the PL/SQL stored procedures I'm using, so I wrote another method to close a cursor, as follows: 

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'};
    $sth->bind_param_inout(':cur', \$cursor, 0, { ora_type => ORA_RSET
+ } );
    if ($sth->execute) {
        return 1;
    } else {
        warn "Error closing cursor: ".$sth->errstr."\n";
        return 0;
    }
}

[download]

I call this method with the inout parameter I passed to the stored proc called previously. I can provide that code if necessary.

I'm receiving this output when calling CloseCursor as above: 

Error closing cursor: ORA-01023: Cursor context not found (Invalid cur
+sor number) (DBD: odescr failed)

[download]

What does this indicate I'm doing wrong?

Thanks for your advice.

Replies are listed 'Best First'.
Re: Closing Oracle cursors
by sasikumar (Monk) on Apr 19, 2005 at 16:44 UTC
    Hi

    I am not a oracle expert. But still i hope the problem is oracle is not able to find the cursor to close. so it means either the cursor you are trying to close is already closed or oracle does not provide u to close a cursor this way. Upto my understading in my degree a cursor opened inside a PL/SQL block (BEGIN .... END) has to be closed within the block.
    Hope this helps
    Its not releated to perl any way????


    Thanks
    SasiKumar
[reply]
Re: Closing Oracle cursors
by RazorbladeBidet (Friar) on Apr 19, 2005 at 17:11 UTC
    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?
    --------------
    "But what of all those sweet words you spoke in private?"
    "Oh that's just what we call pillow talk, baby, that's all."
[reply]

      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"
+;
    }
    
}

      [download]

      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

      [download]

      And there CloseCursor prints the error noted before.

      What do you think?

[reply]
[d/l]
[select]
        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?
[reply]
Re: Closing Oracle cursors
by mickey (Acolyte) on Apr 21, 2005 at 20:35 UTC

    Update: I've had a productive discussion on this subject on the dbi-users mailing list. If you're interested, please see this thread.

    The conclusions were:

    1. Oracle does close ref cursors automatically when the variable holding them goes out of scope;
    2. I probably need a version of DBD::Oracle compiled to use the Oracle 8 API; and
    3. my problem is probably to do with a reference being left open somewhere.

    Hope this is helpful to someone.

[reply]

Back to Seekers of Perl Wisdom