Michael A Chase tech wrote:
On 03/16/2005 08:25 AM, Charles Jardine said:
perldoc DBD::Oracle says, under "Binding Cursors":
To close the cursor you (currently) need to do this:
$sth3 = $dbh->prepare("BEGIN CLOSE :cursor; END;");
$sth3->bind_param_inout(":cursor", \$sth2, 0, { ora_type =>
ORA_RSET } );
$sth3->execute;
This works, and demonstrates that a cursor got from PL/SQL
may be passed back to PL/SQL. However, I doubt that it is
necessary. Tracing shows that, if I let $sth2 go out of scope,
the destructor frees the OCI statement handle. Surely this
will cause the cursor's resources will be released.
Does anyone know why this was once thought to be necessary?
It frees the client side handle, but probably doesn't tell the RDBMS
that the cursor is no longer needed.
Why not? OCIHandleFree normally frees server-side resources.
I have demonstrated to my own satisfaction (using Oracle 9.2.0.6)
that the statement handle destructor does indeed close a cursor
obtained from PL/SQL, and that the explicit close is unnecessary.
The demonstration program and output are below. I will submit
a patch to the documentation.
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, he 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.)
===================================
#!/bin/perl -w
use strict;
use DBI;
use DBD::Oracle qw(:ora_types);
sub show {
my ($dbh, $where) = @_;
my $cursors = $dbh->selectcol_arrayref(
'select c.sql_text from v$open_cursor c,
(select distinct sid from v$mystat) s where c.sid = s.sid'
);
print "$where: \n Cursors:\n ", join("\n ", @$cursors), "\n";
}
my $dbh = DBI->connect('dbi:Oracle:', 'system', '********',
{ RaiseError=>1, PrintError=>0, AutoCommit => 0 });
show($dbh, 'Connected');
my $sth = $dbh->prepare('begin open :cursor for select * from dual; end;');
show($dbh, 'plsql cursor prepared');
$sth->bind_param_inout(':cursor', \my $cursor, 0, { ora_type => ORA_RSET });
show($dbh, 'plsql cursor bound');
$sth->execute;
show($dbh, 'plsql cursor executed');
$sth = undef;
show($dbh, 'plsql cursor destroyed');
print @{$cursor->fetch}, "\n";
show($dbh, 'inner cursor fetched');
$cursor = undef;
show($dbh, 'inner cursor destroyed');
$dbh->disconnect;
===================================
Connected:
Cursors:
select c.sql_text from v$open_cursor c, (select d
insert into sys.aud$( sessionid,entryid,statement,timestamp#
plsql cursor prepared:
Cursors:
select c.sql_text from v$open_cursor c, (select d
insert into sys.aud$( sessionid,entryid,statement,timestamp#
plsql cursor bound:
Cursors:
select c.sql_text from v$open_cursor c, (select d
insert into sys.aud$( sessionid,entryid,statement,timestamp#
plsql cursor executed:
Cursors:
select c.sql_text from v$open_cursor c, (select d
SELECT * FROM DUAL
begin open :cursor for select * from dual; end;
insert into sys.aud$( sessionid,entryid,statement,timestamp#
plsql cursor destroyed:
Cursors:
select c.sql_text from v$open_cursor c, (select d
SELECT * FROM DUAL
insert into sys.aud$( sessionid,entryid,statement,timestamp#
X
inner cursor fetched:
Cursors:
select c.sql_text from v$open_cursor c, (select d
SELECT * FROM DUAL
insert into sys.aud$( sessionid,entryid,statement,timestamp#
inner cursor destroyed:
Cursors:
select c.sql_text from v$open_cursor c, (select d
insert into sys.aud$( sessionid,entryid,statement,timestamp#
===================================
--
Charles Jardine - Computing Service, University of Cambridge
[EMAIL PROTECTED] Tel: +44 1223 334506, Fax: +44 1223 334679
