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

[enoch]

And the answer:

While one cannot pass back package-defined SQL variable types from Oracle stored procedures and functions, one can pass back cursors. And, you can define the cursor any way you see fit. So, here's how to accomplish the above.

First, you need to redefine the procedure to not return a "TABLE" type; rather, it should return a cursor (*note* I changed the procedure to a function because it seemed better that way now that it is just returning a var).

CREATE OR REPLACE PACKAGE foo AS

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

Your package body, then simply becomes:

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;

To get at this from Perl, you use the following 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::errst
+r\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;

I'll leave this here for posterity just in case anyone else ever stumbles upon this particular problem and encounters Warnock's Dilemma.

enoch

[Anonymous Monk]

Since i am calling a remote stored procedure in PERL through a DB link. One of the OUT parameter is a record of type table. Is there any way of fetching this in PERL.I can only return a cursor if i call a local procedure first and then write the data in a table which i don't want to do.

[nbartusi]

I've found that I also had to add this code to the perl script:

use DBD::Oracle qw(:ora_types);