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]

In reply to Retrieving result sets using stored procedures (DBI, DBD::Oracle) by gsiems

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.