I finally figured this one... wow is there a lot of bad documentation out there in regards to this... but in a nutshell here is the jist of what needs to happen to get a cursor to work properly...

1) Create a package of with the stored proc. I've copied a generic one below to use for reference.

CREATE OR REPLACE PACKAGE table_ids AS
   TYPE return_cursor IS REF CURSOR;
   PROCEDURE get_next_session_id(session_cursor OUT return_cursor); 
END table_ids;
/
CREATE OR REPLACE PACKAGE BODY table_ids AS 
PROCEDURE get_next_session_id(session_cursor OUT return_cursor) IS
   BEGIN
      OPEN session_cursor FOR
      SELECT
             'S'||lpad(session_id.nextval,5,'0')
      FROM dual;
      EXCEPTION
         WHEN NO_DATA_FOUND
              THEN raise_application_error(-20000, 'No more rows.');
         WHEN OTHERS
              THEN raise_application_error(-20001, 'There was an undet
+erminable error.');
END get_next_session_id;
[download]

2)Use the following example of code to bind param's. This one is setup such that I can use it for any stored proc. If you you're just interested in using it once and a while you can peel out most of the loops...

sub executeStoredProcTestInfo
{
   $input_parameters = shift;
   $num_of_output_cursors = shift;
   $stored_proc_name = shift;
   $schema = "LOGIN";
   $password = "PSWD";
   $database = "dbi:Oracle:DBNAME";
   
   $dbh = createConnectionToDB($database, $schema, $password);
   $sql.= "BEGIN $stored_proc_name(";
   for ($i=1; $i<=scalar(@$input_parameters)+scalar(@$num_of_output_cu
+rsors); $i++) 
   {
      if($i!= scalar(@$input_parameters)+scalar(@$num_of_output_cursor
+s))
      {
         $sql.=":temp_".$i;
     $sql.=",";
      }
      else
      {
         $sql.=":temp_".$i;
      }
   }
   
   $sql.="); END;";
   
   #print $sql,"\n";
   
   $sth = $dbh->prepare($sql);
   $counter = 1;
   @var_size_array = @$num_of_output_cursors;
   @var_input_array = @$input_parameters;
   
   foreach $item (@var_input_array)
   {
      $var = $counter - 1;
      $code = "\$sth->bind_param\(\":temp_$counter\", $item \)\;";
      eval $code ;
      #print $code, "\n";
      $counter++;  
   }
   
   $counter_set = $counter;
   foreach $item (@$num_of_output_cursors) 
   {
      $code="\$sth->bind_param_inout(\":temp_$counter\", \\\$temp_$cou
+nter, 0, \{ ora_type => ORA_RSET \})\;";
      eval $code ;
      #print $code, "\n";
      $counter++;
   }
   
   $sth->execute();
   
   foreach $item (@$num_of_output_cursors) 
   {
      $code = "\$table = \$temp_$counter_set->fetchall_arrayref\;";
      eval $code ;
      #print $code, "\n";
      $counter_set++;
   }
   $sth->finish;
   endConnection($dbh);
   
   
   return $table;
}
[download]

3)Run with it... alternatively if you just need to run a stored proc that either has a one row return or no return you could put something together like this example:

STORED PROC

CREATE OR REPLACE PROCEDURE     get_next_session_id
(v_session_id OUT VARCHAR2)

IS
  BEGIN
  SELECT
         'S'||lpad(session_id.nextval,5,'0')
     INTO
         v_session_id
     FROM dual;
  EXCEPTION
     WHEN NO_DATA_FOUND
          THEN raise_application_error(-20000, 'No more rows.');
     WHEN OTHERS
          THEN raise_application_error(-20001, 'There was an undetermi
+nable error.');
END get_next_session_id;
[download]

PERL code

my $rv;
my $func = $dbh->prepare(q{
        BEGIN
            get_next_session_id(:rv);
        END;
    });

    $func->bind_param_inout(":rv", \$rv, 6);
    $func->execute;
[download]

Finally I want to give credit where credit is due... this is the link where I finally found enough information to put two and two together.

http://archive.develooper.com/dbi-dev@perl.org/msg00235.html

CHEERS

---

---

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! —

• How do I compose an effective node title?
• How do I post a question effectively?
• Markup in the Monastery

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:
	&		&
	<		&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.