Binding "Out" Parameters to Table Types for Oracle PL/SQL Stored Procedures

[enoch]

I am having a problem getting Perl to bind OUT parameters correctly to Oracle stored procedures using DBD::Oracle. Everything works fine when I am binding them to normal PL/SQL variable types such as NUMBER, VARCHAR2, etc. The real gotcha comes when I try to bind parameters to data types defined in an Oracle package as:

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

For example, say I create the ultra-simplistic package:

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;

The Perl code to execute this and pull out the name of the department (dept_name) is simple and works just fine:

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::errs
+tr\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;

That code correctly prints out the department name. Now, comes the kicker, let's say I want to write a procedure that returns all of the departments. That is, it returns the whole department table. Something like:

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;

It's a fairly simple procedure and works just fine. Now, I write the Perl code to execute it and gather the results.

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::errs
+tr\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;

This returns the error that I am not using the right data type.

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)

So, my question is, how (assuming there is a way) can you bind Perl scalars to Oracle PL/SQL procedure OUT variables that are of a package-defined type? Can you? I have read over DBD::Oracle's POD examples, and I have read all the other pages I could find (Perl DBI Examples, DBI DBD::Oracle and OraPerl FAQ, etc.); and I can't find anything. Is there any Perl/Oracle-guru lurking around the Monastery?

Thanks,
enoch