Tim wrote, on 20/04/2005 21:46:
On Wed, Apr 20, 2005 at 03:46:20PM -0400, Michael Styer wrote:
What about this:
http://search.cpan.org/~timb/DBD-Oracle-1.16/Oracle.pm#Binding_Cursors
At the end of the "Binding Cursors" section it says that cursors need to
be (and can be) closed with code like this:
$sth3 = $dbh->prepare("BEGIN CLOSE :cursor; END;");
$sth3->bind_param_inout(":cursor", \$sth2, 0, { ora_type => ORA_RSET }
);
$sth3->execute;
That's where I got one of the methods I've tried for closing the cursor.
I've also had other people who know Oracle but not Perl tell me there's
no way that should work, but it's in the docs. So that's why I'm
confused. Some people tell me I can't close my cursors this way, others
say that I can't close cursors at all, and others say that cursors get
closed automatically by the Perl garbage collector.
Is anyone able to shed some light on the situation?
The t/50cursor.t test script in the DBD::Oracle distribution uses that
code to close the cursors that it has opened.
http://search.cpan.org/src/TIMB/DBD-Oracle-1.16/t/50cursor.t
So it "works" at least in the sense of not failing.
The test script doesn't then check how many cursors Oracle thinks
are open. Patches to add that are welcome.
I'd also accept a patch to close fetched cursors automatically when the
statement handle is destroyed ($sth2 in the example above). I'm not sure
if it doesn't owing to a bug or an oversight.
I've got there!
There is a significant difference between the documentation which
Michael quotes and the test which Tim mentions. The test binds the
cursor to the close handle with
$close_cursor->bind_param( ":kursor", $cursor, { ora_type => ORA_RSET })
The documentation suggests using bind_param_inout. The documentation
is plain wrong. There cannot be a reference to a closed cursor,
so Oracle is correct to refuse to attempt to return one.
I find that Michael's example posted in another message in this
thread works if the bind is changed to match the test, rather than
the documentation.
I am still convinced that Michael should not need explicitly to
close his cursors. I would be interested to know if tracing shows
his cursors being destroyed in a timely fashion, since I suspect
that something may be maintaining references to the DBI handles for
longer than necessary.
--
Charles Jardine - Computing Service, University of Cambridge
[EMAIL PROTECTED] Tel: +44 1223 334506, Fax: +44 1223 334679
