Beefy Boxes and Bandwidth Generously Provided by pair Networks
Keep It Simple, Stupid
 
PerlMonks  

Re^2: Closing Oracle cursors

by mickey (Acolyte)
on Apr 19, 2005 at 17:29 UTC ( [id://449349]=note: print w/replies, xml ) Need Help??


in reply to Re: Closing Oracle cursors
in thread Closing Oracle cursors

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?

Replies are listed 'Best First'.
Re^3: Closing Oracle cursors
by Transient (Hermit) on Apr 19, 2005 at 18:00 UTC
    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?

      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... :)

        Hmm.. I'm wondering if there isn't some type of error upstream.

        I assume you're able to iterate through the cursor?

        Can you manually open a cursor and retrieve it and then close it? (i.e. without using AUTOLOAD - just hardcode some sql string, pass it in, loop through the cursor, call CloseCursor?)

        How about using RaiseError => 1?

        If I get a moment to test here, I will give it a shot.

Log In?
Username:
Password:

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

How do I use this?Last hourOther CB clients
Other Users?
Others contemplating the Monastery: (5)
As of 2024-03-28 20:53 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found