TYPE myTable IS TABLE OF DeptTable%rowtype INDEX BY BINARY_INTEGER;

##</code><code>##

CREATE OR REPLACE PACKAGE foo AS

  PROCEDURE getDept(dept_id IN NUMBER, dept_name OUT VARCHAR2);
  
END foo;

CREATE OR REPLACE PACKAGE BODY foo AS

    PROCEDURE getDept(dept_id IN NUMBER, dept_name OUT VARCHAR2) IS
    BEGIN
    
         SELECT dept INTO getDept.dept_name
         FROM Dept
         WHERE idDept = getDept.dept_id;
    
    END getDept;

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 $deptName;

eval {
	my $func = $dbh->prepare(q{
		BEGIN
			foo.getDept
			(
				:parameter1,
				:parameter2
			);
		END;
	});

	$func->bind_param(":parameter1", 9);
	$func->bind_param_inout(":parameter2", \$deptName, 4); # bind this parameter as an "inout"
	$func->execute;
};

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

$dbh->disconnect;

##</code><code>##

CREATE OR REPLACE PACKAGE foo AS

  -- PROCEDURE getDept(dept_id IN NUMBER, dept_name OUT VARCHAR2);
  
  /*
    This is a package wide variable type that can hold the contents
    of the entire department table
  */
  TYPE deptTable IS TABLE OF dept%rowtype INDEX BY BINARY_INTEGER;

  PROCEDURE getAllDept(dept OUT deptTable);
  
END foo;

CREATE OR REPLACE PACKAGE BODY foo AS

    PROCEDURE getAllDept(dept OUT deptTable) IS
        CURSOR cursDept IS
            SELECT * FROM dept;
            
        i NUMBER := 0;
    BEGIN
         
        OPEN cursDept;
        LOOP
            FETCH cursDept INTO getAllDept.dept(i); /* put all of the dept table's records in the variable */
            i := i + 1;
            EXIT WHEN cursDept%NOTFOUND;
        END LOOP;
        
        CLOSE cursDept;
    END getAllDept;

END foo;

##</code><code>##

use Data::Dumper;

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
			foo.getAllDept
			(
				:parameter1
			);
		END;
	});

	$func->bind_param_inout(":parameter1", \$deptRecords, 4096); 
	$func->execute;
};

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

$dbh->disconnect;

##</code><code>##

Execution of stored procedure failed because:
ORA-06550: line 3, column 4:
PLS-00306: wrong number or types of arguments in call to 'GETALLDEPT'
ORA-06550: line 3, column 4:
PL/SQL: Statement ignored (DBD: oexec error)