Beefy Boxes and Bandwidth Generously Provided by pair Networks
Clear questions and runnable code
get the best and fastest answer

Re: Closing Oracle cursors

by RazorbladeBidet (Friar)
on Apr 19, 2005 at 17:11 UTC ( #449345=note: print w/replies, xml ) Need Help??

in reply to Closing Oracle cursors

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."

Replies are listed 'Best First'.
Re^2: Closing Oracle cursors
by mickey (Acolyte) on Apr 19, 2005 at 17:29 UTC

    I'm working on Windows XP, with Oracle 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?

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

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

        As for the 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... :)

Log In?

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: note [id://449345]
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others meditating upon the Monastery: (2)
As of 2023-04-02 08:07 GMT
Find Nodes?
    Voting Booth?

    No recent polls found