Michael A Chase wrote, on 27/04/2005 13:42:
On 04/27/2005 03:01 AM, Charles Jardine said:
Here is a patch to the pod documentation of DBD::Oracle-1.16.
The patch alters the section headed 'Binding Cursors'.
> diff -ur DBD-Oracle-1.16.cursor-docs/Oracle.pm DBD-Oracle-1.16/Oracle.pm
Your diff has the source files reversed.
Oops. Here's the correct version.
--
Charles Jardine - Computing Service, University of Cambridge
[EMAIL PROTECTED] Tel: +44 1223 334506, Fax: +44 1223 334679
diff -ur DBD-Oracle-1.16/Oracle.pm DBD-Oracle-1.16.cursor-docs/Oracle.pm
--- DBD-Oracle-1.16/Oracle.pm 2004-10-21 20:07:53.000000000 +0100
+++ DBD-Oracle-1.16.cursor-docs/Oracle.pm 2005-04-26 17:19:35.384862000
+0100
@@ -2393,12 +2393,13 @@
=head1 Binding Cursors
Cursors can be returned from PL/SQL blocks. Either from stored
-procedure OUT parameters or from direct C<OPEN> statements, as show below:
+functions (or procedures with OUT parameters) or
+from direct C<OPEN> statements, as shown below:
use DBI;
use DBD::Oracle qw(:ora_types);
- $dbh = DBI->connect(...);
- $sth1 = $dbh->prepare(q{
+ my $dbh = DBI->connect(...);
+ my $sth1 = $dbh->prepare(q{
BEGIN OPEN :cursor FOR
SELECT table_name, tablespace_name
FROM user_tables WHERE tablespace_name = :space;
@@ -2409,7 +2410,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 ( @row = $sth2->fetchrow_array ) { ... }
+ while ( my @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>.
@@ -2417,33 +2418,46 @@
"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:
+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).
# Create the function that returns a cursor
- $sth1 = $dbh->prepare(q{
- CREATE OR REPLACE FUNCTION sp_ListEmp RETURN types.cursorType
- AS l_cursor types.cursorType;
+ $dbh->do(q{
+ CREATE OR REPLACE FUNCTION sp_ListEmp RETURN SYS_REFCURSOR
+ AS l_cursor SYS_REFCURSOR;
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
- $sth1 = $dbh->prepare(q{BEGIN :cursor := sp_ListEmp; END;});
+ my $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 ( @row = $sth2->fetchrow_array ) { ... }
+ while ( my @row = $sth2->fetchrow_array ) { ... }
-To close the cursor you (currently) need to do this:
+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:
- $sth3 = $dbh->prepare("BEGIN CLOSE :cursor; END;");
- $sth3->bind_param_inout(":cursor", \$sth2, 0, { ora_type => ORA_RSET } );
+ my $sth3 = $dbh->prepare("BEGIN CLOSE :cursor; END;");
+ $sth3->bind_param(":cursor", $sth2, { 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.
