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.
--
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 01:43:46.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,7 +1760,15 @@
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:
--- Oracle.pm-orig 2003-09-23 11:22:42.000000000 -0700
+++ Oracle.pm 2003-12-10 01:49:22.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,7 +2168,15 @@
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:
