Trouble with stored procedure data return

[Vorce, Tim (T.)]

I'm trying to do a select within a stored procedure.  The problem is in getting 
results back.   It may have something to do with trying to access a table of returned 
values, and my colleague set this up to work with microsoft access, so I'm sure it 
must work via perl.


The direct select statement works fine:
#!/usr/local/bin/perl
use DBI;
require "/proj/dsa/www/shared/perl/config.ph";
$dbh = DBI->connect($main::DB_DATASOURCE, $main::DB_ROOT,$main::DB_ROOT_PWD,'Or
acle');

$uid="h-grange";
print "uid:$uid\n";

$cursor = $dbh->prepare(" select DSAA02_APPLICATION_C,DSAA03_TOKEN_C from mdsaa
11_stusraptkn where DSAA14_USERID_C='$uid'");

$cursor->execute();

$array_ref=$cursor->fetchall_arrayref();
#print "cursor execute is ok<br>";
$dbh->disconnect;
foreach $row (@$array_ref) {
 ($app,$tok)=@$row;
 print "app,tok:$app,$tok\n";
}
$dbh->disconnect;

Trying to access it via stored procedure never produces any data:
#!/usr/local/bin/perl
use DBI;
require "/proj/dsa/www/shared/perl/config.ph";
$dbh = DBI->connect($main::DB_DATASOURCE, $main::DB_ROOT,$main::DB_ROOT_PWD,'Oracle');

$cursor = $dbh->prepare("begin sp_Sel_mdsaa11_table.get_token1()");

print "cursor:$cursor\n";

$cursor->execute();
$array_ref=$cursor->fetchall_arrayref();
print "array_ref:$array_ref\n";

foreach $row (@$array_ref) {
 print "row:$row\n";
  foreach $tok (@{$row}){
  print "tok:$tok\n";
 }
}
$dbh->disconnect;

The stored procedure is a bit strange, but should produce a table of the results:

CREATE OR REPLACE PACKAGE sp_Sel_MDSAA11_table
 AS
   TYPE token is TABLE of VARCHAR2(100)
   INDEX BY BINARY_INTEGER;
   PROCEDURE get_token1(t_tok OUT token);
   PROCEDURE get_token2(strName IN VARCHAR, strApp IN VARCHAR, t_tok OUT token)
;
   PROCEDURE get_user_app_token(strName IN VARCHAR, strApp IN VARCHAR, t_tok OU
T token, t_count OUT token, t_site OUT token, t_sitet OUT token);
   PROCEDURE get_user_token(strName IN VARCHAR, t_app OUT token, t_tok OUT toke
n, t_count OUT token, t_site OUT token, t_sitet OUT token);
   PROCEDURE get_user_app(strName IN VARCHAR,  t_app OUT token);
END sp_Sel_MDSAA11_table;
/
create or replace package body sp_Sel_MDSAA11_table
as
PROCEDURE get_token1(t_tok OUT token)
IS
CURSOR token_cur IS
            SELECT *
            FROM mdsaa11_stusraptkn WHERE DSAA14_USERID_C = 'h-grange';

percount NUMBER DEFAULT 1;
BEGIN
    FOR single_token IN token_cur
    LOOP
            t_tok(percount) := single_token.DSAA02_APPLICATION_C;
            percount := percount + 1;
    END LOOP;
END;


END;
/

The output I get from this last is attempt is:
cursor:DBI::st=HASH(0x1aa660)
array_ref:ARRAY(0x186e54)

Which begs the question, if $array_ref is an array, why won't the foreach loop produce 
data?  There are some 8 rows that should come through in the select.

Tim Vorce
[EMAIL PROTECTED]
313-248-9985