invoking a stored procedure with input parameter and out cursor in perl script

Question

Trying to execute a procedure in perl script, Proceure ->create or replace

PROCEDURE Getproc
(
    v_catg IN CHAR DEFAULT NULL,
    v_cursor OUT SYS_REFCURSOR
)

1. to execute procedure

   my $sth = $dbh->prepare(
       q{
           BEGIN
           Getproc(:category, :curs);
           END;
       }
   );
   

2. to bind i/p and cursor

   $sth->bind_param(":category", $category1);
   
   $sth->bind_param_inout(":curs", \$cursrecords, 0, {ora_type => ORA_RSET});
   
   $sth->execute;
   $sth->finish; 
   

3. Fetch records from cursor

   while ($hashRef = $cursrecords->fetchrow_hashref) {
       foreach (keys %$hashRef) {
           print "hashref:$hashRef and $_ is $hashRef->{$_}\n";
       }
   }
   

3rd step is not retrieving anything. curserecords=DBI::st=HASH(0x2371bd0) this has hash object. Can some one help me know what is missing here. Why am I not able to retrieve rows from table stored in cursor?

---

PROCEDURE for reference

create or replace PROCEDURE GetProc
(
    v_catg IN CHAR DEFAULT NULL,
    v_cursor OUT SYS_REFCURSOR
)
AS
BEGIN
IF ( v_catgIS NULL ) THEN
    BEGIN
        OPEN  v_cursor FOR
        SELECT EnvVar,
        VALUE 
        FROM table;
    END;
ELSE
    BEGIN
        OPEN  v_cursor FOR
        SELECT EnvVar ,
        VALUE 
        FROM table
        WHERE  Category = v_catg ;
    END;
END IF;

EXCEPTION
    WHEN OTHERS THEN raise_application_error(-20002,SQLCODE||':'||SQLERRM);
END;

Answer 1

Please try (as in René Nyffenegger's collection)

my $sth = $dbh->prepare(
  q{
    DECLARE
      curs sys_refcursor;
    BEGIN
      Getproc(:category, :curs);
    END;
  });

Answer 2

this is my solution, using a stored procedure with mysql and invoked for perl

CREATE DEFINER=`fact`@`%` PROCEDURE `ESTE`(
    IN `DENTRO` INT,
    OUT `UNO` INT,
    OUT `DOS` VARCHAR(50)
)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
 SET UNO = DENTRO;
 SET DOS = 'TODO OK';
END

Code in perl
################################################################################
#Funcion para establecer la conexion con la base de datos
sub conectaServer{
    (my $dbase_db, my $ip_db, my $user_db, my $pass_db, my $port) = @_;
    my $connectionInfo = "DBI:mysql:database=$dbase_db;$ip_db:$port";
    my $dbh = DBI->connect($connectionInfo,$user_db,$pass_db);
    return $dbh;
}

    

####################################################################
sub store{
    my $v = 10;
    my $query = "CALL ESTE(?, \@uno, \@dos)";
    my $dbh = $S_FACTURA->prepare( $query );
    $dbh->bind_param(1, $v);
    $dbh->execute();

    my $query = " SELECT \@uno, \@dos";
    my $dbh = $S_FACTURA->prepare( $query );
    $dbh->execute();
    my @out = $dbh->fetchrow_array();
    print("$out[0]   $out[1]\n");
}


########################## M A I N ##############################
$S_FACTURA = &conectaServer($d_bFACTURA, $d_hFACTURA, $d_uFACTURA, $d_pFACTURA, $d_portFACTURA );
&store();
$S_FACTURA->disconnect()