Beefy Boxes and Bandwidth Generously Provided by pair Networks
XP is just a number
 
PerlMonks  

Re^3: Closing Oracle cursors

by Transient (Hermit)
on Apr 19, 2005 at 18:00 UTC ( [id://449359]=note: print w/replies, xml ) Need Help??


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

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?

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

    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.

        Yes, I can retrieve data from the cursor without a problem.

        I tried modifying the curref.pl example script, and I get the following error:

        The package has been created... The package body has been created... These are the results from the ref cursor: 0 rows Use of uninitialized value in subroutine entry at curref.bat line 83. DBD::Oracle::st dump_results warning: at curref.bat line 83. Unable to SELECT from SYS.V_$OPEN_CURSOR: User testuser needs SELECT p +ermission. DBD::Oracle::st execute failed: ORA-01008: not all variables bound (DB +D: oexec error) at curref.bat line 93.

        So from that error message, one problem is that the user I'm connecting as doesn't have the appropriate permissions. In my workplace getting that permission will be difficult, I think.

        But the last error message is noteworthy -- that's what happens when the procedure to close the cursor is called, and it's exactly what happens when I try to do the same using bind_param() instead of bind_param_inout().

        There's only one variable, and I'm binding it, so I don't know why it thinks I'm not binding all variables.

        Could this be a problem in my version of Perl, DBI, or DBD::Oracle?

Log In?
Username:
Password:

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

How do I use this?Last hourOther CB clients
Other Users?
Others avoiding work at the Monastery: (5)
As of 2024-03-29 00:03 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found