Passing and getting array parameters/return values If we want to pass or return arrays, DBI binding does not support it directly. However, there is a trick how we can do it, both for input parameters, and return/output parameters. This is basically idea:

a) For output parameters, we use reference cursor instead of directly returning the array ( or any other collection). We will show an example for array, but you can easily make it for associative array.

b) For input parameters, we do not try to bind them directly. Instead, we loop through Perl array/hash, and create a string, which can be used in PL/SQL Perl block string. So we simply call the PL SQL procedure/function directly.

Example: We define the type STRING_TABLE on the schema level, in the schema TAXNET:

CREATE OR REPLACE
TYPE STRING_TABLE AS TABLE OF VARCHAR2(50);
……

We define the following function in the package RV_TEST:

  FUNCTION collectionInputExampleTable( inputList IN STRING_TABLE) RET
+URN sys_refcursor AS
    tableCollection STRING_TABLE:=STRING_TABLE();
    retCur sys_refcursor;
  BEGIN
    
    FOR i IN 1..inputList.count LOOP
      tableCollection.extend(1);
      tableCollection(i):=inputList(i);
      --outputList(i):=inputList(i);
      --DBMS_OUTPUT.put_line('ELEMENT:'||inputList(i));
    END LOOP;
    
    OPEN retCur FOR SELECT * FROM TABLE (tableCollection);
    
    return retCur;
    
  EXCEPTION WHEN OTHERS THEN
      txnvs_db_util.tx_log( 3, 2, SQLCODE, SQLERRM, null );
      RAISE;
  END collectionInputExampleTable;
[download]

Input array parameter:

The example shows what we need to do:

…..
print "\n============Stored Procedure=========\n";

my @arrParam=('Dusica 1','Dusica 2',"Dusica 3","Dusica 4","Dusica 5");


my $counter=0;
my $plSqlCodeBlock = "
      DECLARE
          inArr    TAXNET.STRING_TABLE:=TAXNET.STRING_TABLE('";
    foreach my $arrEl( @arrParam )
    {
        $plSqlCodeBlock .=$arrEl;
        if($counter<scalar @arrParam-1)
        {
            $plSqlCodeBlock .="','";
        }
        $counter++;
    }
      $plSqlCodeBlock .=    "');
      BEGIN 
           :retVal:= TAXNET.RV_TEST.collectionInputExampleTable(inArr)
+;
      END;"; 

#print "$plSqlCodeBlock\n\n\n";

my $sth = $dbHandle->prepare($plSqlCodeBlock);
my $csr; 

$sth->bind_param_inout( ":retVal", \$csr, 0, { ora_type => ORA_RSET } 
+);
$sth->execute();

while(@row = $csr->fetchrow_array)
{
        #$csr->finish;    
    print "\nList Element:$row[0]";
    
}    

$csr->finish;
$sth->finish;


$dbHandle->disconnect;

print "\n============End=========<<<<<\n";
…..
[download]

Output array parameter/returned value:

We see how we define the cursor to return the collection. We cannot define cursor directly with associative array, but we can “copy” the associative array to the nested table type variable, and after that we can define the cursor with nested table elements. In the function above, we can change inputList to bean associative array too. Now it is easy to get the reference cursor in Perl. We did it in the stoed function above, and this is the line:

…..
    OPEN retCur FOR SELECT * FROM TABLE (tableCollection);
…..
[download]