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
cj10@xxxxxxxxx Tel: +44 1223 334506, Fax: +44 1223 334679
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:
-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.
Re: ORATCL help needed! ...set cursor... Wow, An Oratcl 3.3. ... It is coded with the the OCI layer released with Oracle...Oracle instant client is available for many many platforms, ... (comp.lang.tcl)
Re: Using Cursors ... it seems I've stirred up a hornet's nest with my cursor question.... finding the discussion valuable in my circumstances (moving from Oracle to ...SQL Server).... > DECLARE curEpisode SCROLL CURSOR FOR ... (microsoft.public.sqlserver.programming)
Strange behaviour with SQLBulkOperations ... I'm trying to implement bulk inserts via ODBC. ... I then tried it with Oracle Express, ... // Set the cursor type.... Shouldn't SQL Server and Oracle support bulk operations? ... (microsoft.public.data.odbc)
Re: Using Cursors ... WHERE refcol IN ...SQL Server specific, ... > I have a question about how to replace a cursor with a set based operation. ... I don't really know Oracle well enough to say, ... (microsoft.public.sqlserver.programming)
Re: Accessing a cursor using dynamic SQL ... just do an equal join of the tables and then compare each field, ... It seems that I would use a cursor when ... executing this dynamic sql for the join, but I do not know how to ...Puget Sound Oracle Users Group... (comp.databases.oracle.misc)
