Michael Styler wrote:
So now I'm really confused. In this thread
(http://www.mail-archive.com/[email protected]/msg24109.html)
Charles Jardine suggests that when the variable goes out of scope the
cursor is closed, but you're saying they stay in existence.
and Ron Reidy replied:
Wjile I can agree that hist assertion may be correct, it is indeed, not correct.
> You can see this by querying v$open_cursor and v$sesstat while the program is running.
In my testing, I created a statement handle to return a ref cursor to
> 'select object_name from user_objects'. I then fetched one row from this ref cursor and then:
a) undefed the vref cursor variable
b) called the finish() method on the variable
In both cases, the cursor remained open (as seen in v$open_cursor).
When I called disconnect() on my database handle, the cursors were closed.
Later in the thread refered to above
(in http://www.mail-archive.com/[email protected]/msg24131.html)
I wrote:
Anyone trying to reproduce this should be aware that it is easy
to become confused when looking at V$OPEN_CURSOR from another
session. The server-side effects of an OCIHandleFree are delayed
until the client next _has_ to talk to the server. In the example
below, the cursors are not actually closed at the time of
destruction of the statement handles. They are closed when
the client contacts the server to prepare the select
which fetches the list of open cursors. (Perhaps it was
this effect that confused the author of the documentation.)
Perhaps Ron Reidy has been confused by this effect, or perhaps
he has not managed to get rid of every reference to the statement
handle in question.
Oracle _will_ close these cursors automatically. To get this
to happen you need to
1. Ensure that the DBI statement handle representing the
cursor is DESTROYed. Finishing it is not enough.
2. Then do something else which causes forces an interaction
between the client and the server.
Note that bind_param_inout creates a reference from the pl/sql
statement handle to the cursor statement handle, so the last
cursor retrieved will not be destroyed until the pl/sql handle
is destroyed.
Below is a complete, stand alone, example which retrieves 1500
cursor references from pl/sql, and does not explicitly close
any of them. It does not run out of cursors. (max_open_cursors
is 20 in my testing database.)
The versions I used were as follows:
Oracle: 9.2.0.6
Perl: 5.8.6
DBI: 1.46
DBD::Oracle 1.16
The situation may be different in Oracle 8. I don't have an
instance available for a test.
====================================================
#!/bin/perl -w
use strict;
use DBI;
use DBD::Oracle qw(:ora_types);
my $dbh = DBI->connect('dbi:Oracle:', 'scott', 'tiger',
{RaiseError=>1, AutoCommit=>0, PrintError=>0});
$dbh->do(q{
CREATE OR REPLACE PROCEDURE make_cursor(i NUMBER, c OUT sys_refcursor) IS
BEGIN
OPEN c FOR SELECT i FROM dual;
END;
});
{ my $proc = $dbh->prepare(q{BEGIN make_cursor(:i, :c); END;});
for (my $i = 0; $i < 1500; $i++) {
my $cursor;
$proc->bind_param(':i', $i);
$proc->bind_param_inout(':c', \$cursor, 0, {ora_type=>ORA_RSET});
$proc->execute;
print $cursor->fetchrow_array, "\n";
}
}
$dbh->do(q{DROP PROCEDURE make_cursor});
$dbh->disconnect;
--
Charles Jardine - Computing Service, University of Cambridge
[EMAIL PROTECTED] Tel: +44 1223 334506, Fax: +44 1223 334679
