Re: Oracle ref cursors - documentation patch.
- From: cj10@xxxxxxxxx (Charles Jardine)
- Date: Wed, 27 Apr 2005 16:00:01 +0100
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 cj10@xxxxxxxxx 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.
.
- References:
- Oracle ref cursors - documentation patch.
- From: Charles Jardine
- Re: Oracle ref cursors - documentation patch.
- From: Michael A Chase
- Oracle ref cursors - documentation patch.
- Prev by Date: cPanel / DBI / mySQL / Exim issue
- Next by Date: Re: cPanel / DBI / mySQL / Exim issue
- Previous by thread: Re: Oracle ref cursors - documentation patch.
- Next by thread: DBD::Sybase returns blank character instead of empty string
- Index(es):
Relevant Pages
|
