Oracle Pipeline tables

[c.w.huling]

I have run into an odd issue, using oracle 9.2.0.4, and I am trying a
test based on the example in DBD::Oracle, using pipelined tables.  I
made a small test package:

/*

@TEST_PKG.SQL

SELECT * FROM TABLE (TEST_PKG.F_TEST(6));

var c refcursor
exec TEST_PKG.TEST1(6,:c);
exec TEST_PKG.TEST2(6,:c);
exec TEST_PKG.TEST3(6,:c);
exec TEST_PKG.TEST4(6,:c);

*/

CREATE OR REPLACE PACKAGE TEST_PKG AS

  TYPE CSR IS REF CURSOR;  

  TYPE TESTTYPE IS RECORD (FACILITY_NAME VARCHAR2(5),
                           FACILITY_ID NUMBER);
  TYPE TESTTABLE IS TABLE OF TESTTYPE;

  FUNCTION F_TEST (nCOUNT IN NUMBER) RETURN TESTTABLE PIPELINED;

  PROCEDURE TEST1 (nCOUNT IN NUMBER, cCURSOR OUT CSR);
  PROCEDURE TEST2 (nCOUNT IN NUMBER, cCURSOR OUT CSR);
  PROCEDURE TEST3 (nCOUNT IN NUMBER, cCURSOR OUT CSR);
  PROCEDURE TEST4 (nCOUNT IN NUMBER, cCURSOR OUT CSR);

END;
/
SHOW ERRORS

CREATE OR REPLACE PACKAGE BODY TEST_PKG AS

  FUNCTION F_TEST (nCOUNT IN NUMBER) RETURN TESTTABLE PIPELINED AS
    nI NUMBER;    
    tTEST TESTTYPE;
  BEGIN
    nI:=0;
    FOR nI IN 1..nCOUNT LOOP
      tTEST.FACILITY_NAME:=TO_CHAR(nI);
      tTEST.FACILITY_ID:=nI;
      PIPE ROW (tTEST);
    END LOOP;
    RETURN;
  END;

  PROCEDURE TEST1 (nCOUNT IN NUMBER, cCURSOR OUT CSR) AS
  BEGIN
    OPEN cCURSOR FOR SELECT * FROM TABLE (TEST_PKG.F_TEST(6));
  END;

  PROCEDURE TEST2 (nCOUNT IN NUMBER, cCURSOR OUT CSR) AS
  BEGIN
    OPEN cCURSOR FOR SELECT * FROM TABLE (TEST_PKG.F_TEST(nCOUNT));
  END;

 PROCEDURE TEST3 (nCOUNT IN NUMBER, cCURSOR OUT CSR) AS
   vSQL VARCHAR2(1024):='SELECT * FROM TABLE (TEST_PKG.F_TEST(:1))';
 BEGIN
    OPEN cCURSOR FOR vSQL USING nCOUNT;
  END;

 PROCEDURE TEST4 (nCOUNT IN NUMBER, cCURSOR OUT CSR) AS
  vSQL VARCHAR2(1024);
 BEGIN
    vSQL:='SELECT * FROM TABLE (TEST_PKG.F_TEST('||nCOUNT||'))';
    OPEN cCURSOR FOR vSQL;
  END;

END;
/
SHOW ERRORS


For that package, TEST1 and TEST4 work, which lends to the problem I
am having on the perl side:

#!/usr/local/bin/perl

use DBInit;

my $DBH=DBInit::GetDatabaseHandle('DEV');

my $stmt1=$DBH->prepare('SELECT * FROM TABLE (TEST_PKG.F_TEST(6))');
$stmt1->execute();
my $h=$stmt1->fetchall_hashref('FACILITY_ID');
foreach (keys %{$h}){
    print "$_\n";
}

eval {
    my $stmt2=$DBH->prepare('SELECT * FROM TABLE (TEST_PKG.F_TEST(?))');
    $stmt2->execute(6);
    my $h=$stmt2->fetchall_hashref('FACILITY_ID');
    foreach (keys %{$h}){
        print "$_\n";
    }
};
print "[EMAIL PROTECTED]";

When I try and bind variable I get this.

DBD::Oracle::db prepare failed: 
ORA-22905: cannot access rows from a non-nested table item (DBD ERROR: 
OCIStmtExecute/Describe) [for statement ``SELECT * FROM TABLE (TEST_PKG.F_TEST(?))'']) 
at ./t.pl line 16.

I started investigate which lead to my TEST1-4, and discovered you
can't bind the variables inside either.  Am I doing something wrong?
Doesn't seem to be a usful tool if you can't pass in variables
effectivly.  

I can always make a static string for the call, but that seems more
like a work around, not a solution.


-- 
C Wayne Huling <[EMAIL PROTECTED]>