Greetings,

I am trying to learn how to use the DBI and DBD::Oracle. The current quest is to figure out how to use a stored procedure/function/package to return the results of a multi-row query. All appears well if there are no other arguments to the procedure than the IN OUT for the reference cursor. However, add a calling argument and it no longer works. (FWIW this is using Active States perl v5.6.1 built for MSWin32-x86-multi-thread (Binary build 632).)

Where have I erred?

Here is the output:

The emp_test_pkg package has been created...
The emp_test_pkg package body has been created...

These are the results from the ref cursor (1):
'SMITH     ', 'CLERK    '
'ADAMS     ', 'CLERK    '
'JAMES     ', 'CLERK    '
'MILLER    ', 'CLERK    '
4 rows

These are the results from the ref cursor (2):

0 rows

And here is the script:

#!/usr/bin/perl -w
#####################################################################
#
# Name:    curref2.pl
#
# Summary: Figure out how to get result sets back from Oracle using
#          stored PL/SQL procedures/functions/packages/whatever
#
# History:
#  date       author    comment
#  ---------- --------- ---------------------------------------------
#  2002.06.27 gsiems    created from:
#                       DBD-Oracle-1.12/Oracle.ex/curref.pl
#                       (by Geoffrey Young) then badly hacked...
#####################################################################

use DBI;
use DBD::Oracle qw (:ora_types);
use strict;

my ($inst, $user, $pass) = ("xxxx", "yyyy", "zzzz");

my $dbh = DBI->connect ("dbi:Oracle:$inst", $user, $pass,
    { AutoCommit => 0, RaiseError => 1, PrintError => 0 })
    || die $DBI::errstr;

create_plsql_units ();
test_ref_cursor1 ();
test_ref_cursor2 ();

$dbh->disconnect;

#####################################################################
sub test_ref_cursor1 {
   # Reference cursor...no args...this works:
   print "\nThese are the results from the ref cursor (1):\n";
   my $curref;
   my $sql = qq (
      BEGIN 
         emp_test_pkg.emp_cursor1 (:curref); 
      END;
   );
   my $sth = $dbh->prepare ($sql) || die $dbh->errstr;
   $sth->bind_param_inout (
      ":curref", \$curref, 0, {ora_type => ORA_RSET});
   $sth->execute () || die $sth->errstr;
   DBI::dump_results ($curref);
}

#####################################################################
sub test_ref_cursor2 {
   # Reference cursor...this doesn't work (correctly):
   print "\nThese are the results from the ref cursor (2):\n";
   my $curref;
   my $sql = qq (
      BEGIN 
         emp_test_pkg.emp_cursor2 (:job_in, :curref); 
      END;
   );
   my $sth = $dbh->prepare ($sql) || die $dbh->errstr;
   $sth->bind_param (":job_in", "CLERK");
   $sth->bind_param_inout (
      ":curref", \$curref, 0, {ora_type => ORA_RSET});
   $sth->execute () || die $sth->errstr;
   DBI::dump_results ($curref);
}

#####################################################################
sub create_plsql_units {
   my $sql = qq (
     CREATE OR REPLACE PACKAGE emp_test_pkg
     IS
       TYPE c_ref IS REF CURSOR;
       PROCEDURE emp_cursor1 (curref IN OUT c_ref);
       PROCEDURE emp_cursor2 (job_in IN VARCHAR2, 
                              curref IN OUT c_ref);
     END;
   );
   my $rv = $dbh->do ($sql);
   print "The emp_test_pkg package has been created...\n";

   $sql = qq (
     CREATE OR REPLACE PACKAGE BODY emp_test_pkg
     IS
       PROCEDURE emp_cursor1 (curref IN OUT c_ref)
       IS
       BEGIN
         OPEN curref FOR
              SELECT ename, job FROM emp WHERE job = 'CLERK';
       END;
       PROCEDURE emp_cursor2 (job_in IN VARCHAR2, 
                              curref IN OUT c_ref)
       IS
       BEGIN
         OPEN curref FOR
              SELECT ename, job FROM emp WHERE job = job_in;
       END;

     END;
   );
   $rv = $dbh->do ($sql);
   print "The emp_test_pkg package body has been created...\n";
}
[download]