Problems using DBD::Oracle 1.14, Returning an Oracle 9i RECORD datatype

[Steve Edwards]

Is it possible to return a RECORD datatype from Oracle 9i using
DBD::Oracle 1.14?

I've searched the archives and documentation and can find no mention of
not being able to 
yet I can't get it to work.

I have a package with the following function

        TYPE User_Profile IS RECORD (
                        sid SYS.V_$SESSION.SID%TYPE,
                        login SYS.V_$SESSION.USERNAME%TYPE,
                        role DISPATCHERS.ROLE_ID%TYPE
                );


             FUNCTION UserInfo RETURN User_Profile;



It works fine in PL/SQL but when I use the following perl  v5.8.0 
script with DBD::Oracle 1.14

==================================================
#!/usr/local/bin/perl
#
# Given an adnum and address return a list of customer that match
#
 
 
use DBI;
use DBD::Oracle qw(:ora_types);
use DBD::Oracle qw(:ora_session_modes);
 
my ($dbh,$sth, @row);
 
#Connect to the oms database
$dbh = DBI->connect("dbi:Oracle:oms","oms","oms", {PrintError => 1} )
        or die "Can't connect to Oracle:oms: $DBI::errstr\n";
 
$adnum="6015";
$adnum_quoted=$dbh->quote("%$adnum%");
$adstreet="M";
$adstreet_quoted=$dbh->quote("%$adstreet%");
$gsn='153827417400';
$gsn_quoted=$dbh->quote($gsn);
 
#$sql = "Select * from customer where adnum like $adnum_quoted and
upper(adstreet) like upper($adstreet_quoted)";

$sql = q{ BEGIN
                                :return := oms_pkg.UserInfo;
                        END; };
 
print "$sql\n";
 
$sth = $dbh->prepare($sql) or die "Can't prepare statement:$sql: ",
$dbh->erstr(), "\n";
my $sth2;
$sth->bind_param_inout(":return", \$sth2, 0, { ora_type => ORA_RSET }
);
$sth->execute();
 
#$row=$sth->dump_results();
 
#while ( $row_ref = $sth->fetchrow_hashref('NAME_lc') ) {
#       print
"$row_ref->{namelast},$row_ref->{adnum},$row_ref->{adstreet}\n";
#}
 
while ( $row_ref = $sth->fetchrow_arrayref ) {
        push @row, [ @$row_ref ];
}
 
# Print the array
foreach $row_ref ( @row ) {
foreach $row_ref ( @row ) {
        print "@$row_ref\n";
}
 
#Be correct and disconnect
        $dbh->disconnect or warn "Disconnection failed:
$DBI::errstr\n";
 
exit;
==============================================

I works fine if I return an array of varchar2 as with the select * from
customers statement.
But with a function returning a RECORD datatype I get the following
error


$ ./FindCustomer.prl 
 BEGIN 
                                :return := oms_pkg.UserInfo;
                        END; 
DBD::Oracle::st execute failed: ORA-06550: line 2, column 24:
PLS-00382: expression is of wrong type
ORA-06550: line 2, column 5:
PL/SQL: Statement ignored (DBD ERROR: OCIStmtExecute) [for statement ``
BEGIN 
                                :return := oms_pkg.UserInfo;
                        END; '' with params:
:return=DBI::st=HASH(0x30c8c)]) at ./FindCustomer.prl line 38.
DBD::Oracle::st fetchrow_arrayref failed: ERROR no statement executing
(perhaps you need to call execute first) [for statement `` BEGIN 
                                :return := oms_pkg.UserInfo;
                        END; '' with params:
:return=DBI::st=HASH(0x30c8c)]) at ./FindCustomer.prl line 47.
$ 


Any ideas,

Thanks, 
Steve



[EMAIL PROTECTED]
Chugach Electric Association, Inc
5601 Minnesota Drive
Anchorage, Alaska  99519
907-762-4830