Re^4: Closing Oracle cursors

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;
[download]

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;
[download]

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

Comment on Re^4: Closing Oracle cursors Select or Download Code

Replies are listed 'Best First'.
Re^5: Closing Oracle cursors by Transient (Hermit) on Apr 19, 2005 at 18:57 UTC
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.	[reply]
Re^6: Closing Oracle cursors by mickey (Acolyte) on Apr 19, 2005 at 19:26 UTC
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.` [download] 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?	[reply] [d/l]
Re^7: Closing Oracle cursors by Transient (Hermit) on Apr 19, 2005 at 19:49 UTC
To be honest, I'm not entirely sure. I haven't used cursors/refs wrt DBD::Oracle. What versions of each do you have? The only other suggestions I can offer is to find a DBD::Oracle forum and/or try the following I found in the Changes: Added ability to pass existing DBD::Oracle select statement handle (cursor) back _into_ Oracle as a ref cursor type thanks to Mike Moretti. Note that this enables a workaround for closing ref cursors: $dbh->do("BEGIN CLOSE :cursor; END;", undef, $sth_ref_csr_to_close);	[reply]
Re^8: Closing Oracle cursors by mickey (Acolyte) on Apr 19, 2005 at 20:15 UTC