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 undeterminable error.');
END get_next_session_id;
##</code><code>##

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_cursors); $i++) 
   {
      if($i!= scalar(@$input_parameters)+scalar(@$num_of_output_cursors))
      {
         $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_$counter, 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;
}
##</code><code>##

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 undeterminable error.');
END get_next_session_id;

##</code><code>##

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

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