Re: [PATCH]Misleading PL/SQL Example for Binding Cursors, Rel 2

[Michael A Chase]

On Wed, 10 Dec 2003 14:05:03 +0000 Tim Bunce <[EMAIL PROTECTED]> wrote:

> Great. Thanks!
> 
> I have a plan (hope, dream, etc) to get a DBD::Oracle release out
> before the New Year.
> 
> Tim.
> 
> On Wed, Dec 10, 2003 at 01:55:17AM -0800, Michael A Chase wrote:
> > The example given for returning a cursor from a function is
> > inconsistent with the surrounding example code for handling bound
> > cursors.  The attached patches for DBD::Oracle 1.14 and 1.15 of 23 Sep
> > 2003 correct the inconsistency and add more detail.

As Kimball pointed out, there was a syntax error in the close PL/SQL.
The attached patches replace the original ones.

-- 
Mac :})
** I usually forward private questions to the appropriate mail list. **
Ask Smarter: http://www.catb.org/~esr/faqs/smart-questions.html
Give a hobbit a fish and he eats fish for a day.
Give a hobbit a ring and he eats fish for an age.
--- Oracle.pm-orig	2003-03-25 05:58:00.000000000 -0800
+++ Oracle.pm	2003-12-10 07:27:47.000000000 -0800
@@ -1751,6 +1751,7 @@
 
 Here's an alternative form using a function that returns a cursor:
 
+  # 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;
@@ -1759,11 +1760,19 @@
       RETURN l_cursor;
     END;
   });
-  $sth2 = $dbh->prepare(q{BEGIN :cursor := sp_ListEmp; END;});
+  # CREATE is executed in prepare().
+
+  # Use the function that returns a cursor
+  $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 ) { ... }
 
 To close the cursor you (currently) need to do this:
 
-  $sth3 = $dbh->prepare("BEGIN CLOSE :cursor END");
+  $sth3 = $dbh->prepare("BEGIN CLOSE :cursor; END;");
   $sth3->bind_param_inout(":cursor", \$sth2, 0, { ora_type => ORA_RSET } );
   $sth3->execute;
 

--- Oracle.pm-orig	2003-09-23 11:22:42.000000000 -0700
+++ Oracle.pm	2003-12-10 07:22:38.000000000 -0800
@@ -2159,6 +2159,7 @@
 
 Here's an alternative form using a function that returns a cursor:
 
+  # 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;
@@ -2167,11 +2168,19 @@
       RETURN l_cursor;
     END;
   });
-  $sth2 = $dbh->prepare(q{BEGIN :cursor := sp_ListEmp; END;});
+  # CREATE is executed in prepare().
+
+  # Use the function that returns a cursor
+  $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 ) { ... }
 
 To close the cursor you (currently) need to do this:
 
-  $sth3 = $dbh->prepare("BEGIN CLOSE :cursor END");
+  $sth3 = $dbh->prepare("BEGIN CLOSE :cursor; END;");
   $sth3->bind_param_inout(":cursor", \$sth2, 0, { ora_type => ORA_RSET } );
   $sth3->execute;