CREATE OR REPLACE PACKAGE foo AS

  TYPE deptCurs IS REF CURSOR; 
  
  FUNCTION getAllDept RETURN foo.deptCurs;
END foo;

##</code><code>##

CREATE OR REPLACE PACKAGE BODY foo AS

   FUNCTION getDeptCurs RETURN foo.deptCurs IS
        crsr foo.deptCurs;
    BEGIN
          OPEN crsr FOR SELECT * FROM luDept;
          RETURN(crsr);
    END getDeptCurs;
END foo;

##</code><code>##

my $dbh =DBI->connect('dbi:Oracle:host=hostname;sid=theSID',
       'user',
       'pass',
       {
           RaiseError => 1,
           AutoCommit => 0
       }) || die "Database connection not made because:\n\t$DBI::errstr\n";

my $deptRecords;

eval {
   my $func = $dbh->prepare(q{
       BEGIN
           :curs := foo.getDeptCurs();
       END;
   });
   
   # must specify the ora_type of ORA_RSET
   # so code knows it is a cursor
   $func->bind_param_inout(":curs ", \$deptRecords, 
                   0, { ora_type => ORA_RSET});    
   $func->execute;
   $func->finish;
};

if( $@ ) {
   warn "Execution of stored procedure failed because:\n\t$DBI::errstr\n$@";
}

while(my $hashRef = $deptRecords->fetchrow_hashref)
{
   foreach(keys %$hashRef)
   {
      print "$_ is $hashRef->{$_}\n";
   }
}

$deptRecords->finish;
$dbh->disconnect;