Here is a patch to the pod documentation of DBD::Oracle-1.16.
The patch alters the section headed 'Binding Cursors'.
The patch:
* corrects an error. When passing a cursor back from Perl
to PL/SQL, bind_param_inout _must not_ be used.
* attempts to clear up a misunderstanding. It is not normally
necessary to close cursors explicitly.
It also:
* introduces the Oracle 9 pre-defined type SYS_REFCURSOR.
* makes the examples compatible with 'use strict'.
I have tested my assertion that Oracle closes cursors
automatically. I have not added a test to the test suite
because a proper test requires access to views, including
V$OPEN_CURSOR, which ordinary users cannot see.
--
Charles Jardine - Computing Service, University of Cambridge
[EMAIL PROTECTED] Tel: +44 1223 334506, Fax: +44 1223 334679
diff -ur DBD-Oracle-1.16.cursor-docs/Oracle.pm DBD-Oracle-1.16/Oracle.pm
--- DBD-Oracle-1.16.cursor-docs/Oracle.pm 2005-04-26 17:19:35.384862000
+0100
+++ DBD-Oracle-1.16/Oracle.pm 2004-10-21 20:07:53.000000000 +0100
@@ -2393,13 +2393,12 @@
=head1 Binding Cursors
Cursors can be returned from PL/SQL blocks. Either from stored
-functions (or procedures with OUT parameters) or
-from direct C<OPEN> statements, as shown below:
+procedure OUT parameters or from direct C<OPEN> statements, as show below:
use DBI;
use DBD::Oracle qw(:ora_types);
- my $dbh = DBI->connect(...);
- my $sth1 = $dbh->prepare(q{
+ $dbh = DBI->connect(...);
+ $sth1 = $dbh->prepare(q{
BEGIN OPEN :cursor FOR
SELECT table_name, tablespace_name
FROM user_tables WHERE tablespace_name = :space;
@@ -2410,7 +2409,7 @@
$sth1->bind_param_inout(":cursor", \$sth2, 0, { ora_type => ORA_RSET } );
$sth1->execute;
# $sth2 is now a valid DBI statement handle for the cursor
- while ( my @row = $sth2->fetchrow_array ) { ... }
+ while ( @row = $sth2->fetchrow_array ) { ... }
The only special requirement is the use of C<bind_param_inout()> with an
attribute hash parameter that specifies C<ora_type> as C<ORA_RSET>.
@@ -2418,46 +2417,33 @@
"ORA-06550: line X, column Y: PLS-00306: wrong number or types of
arguments in call to ...".
-Here's an alternative form using a function that returns a cursor.
-This example uses the pre-defined weak (or generic) REF CURSOR type
-SYS_REFCURSOR. This is an Oracle 9 feature. For Oracle 8, you must
-create your own REF CURSOR type in a package (see the C<curref.pl>
-script mentioned at the end of this section).
+Here's an alternative form using a function that returns a cursor:
# Create the function that returns a cursor
- $dbh->do(q{
- CREATE OR REPLACE FUNCTION sp_ListEmp RETURN SYS_REFCURSOR
- AS l_cursor SYS_REFCURSOR;
+ $sth1 = $dbh->prepare(q{
+ CREATE OR REPLACE FUNCTION sp_ListEmp RETURN types.cursorType
+ AS l_cursor types.cursorType;
BEGIN
OPEN l_cursor FOR select ename, empno from emp order by ename;
RETURN l_cursor;
END;
});
+ # CREATE is executed in prepare().
# Use the function that returns a cursor
- my $sth1 = $dbh->prepare(q{BEGIN :cursor := sp_ListEmp; END;});
+ $sth1 = $dbh->prepare(q{BEGIN :cursor := sp_ListEmp; END;});
my $sth2;
$sth1->bind_param_inout(":cursor", \$sth2, 0, { ora_type => ORA_RSET } );
$sth1->execute;
# $sth2 is now a valid DBI statement handle for the cursor
- while ( my @row = $sth2->fetchrow_array ) { ... }
+ while ( @row = $sth2->fetchrow_array ) { ... }
-A cursor obtained from PL/SQL as above may be passed back to PL/SQL
-by binding for input, as shown in this example, which explicitly
-closes a cursor:
+To close the cursor you (currently) need to do this:
- my $sth3 = $dbh->prepare("BEGIN CLOSE :cursor; END;");
- $sth3->bind_param(":cursor", $sth2, { ora_type => ORA_RSET } );
+ $sth3 = $dbh->prepare("BEGIN CLOSE :cursor; END;");
+ $sth3->bind_param_inout(":cursor", \$sth2, 0, { ora_type => ORA_RSET } );
$sth3->execute;
-It is not normally necessary to close a cursor
-explicitly in this way. Oracle will close the cursor automatically
-at the first client-server interaction after the cursor statement handle is
-destroyed. An explicit close may be desirable if the reference to
-the cursor handle from the PL/SQL statement handle delays the destruction
-of the cursor handle for too long. This reference remains until the
-PL/SQL handle is re-bound, re-executed or destroyed.
-
See the C<curref.pl> script in the Oracle.ex directory in the DBD::Oracle
source distribution for a complete working example.
