Steve,
As far as I know, the current version of DBD::Oracle does *not* support
Record types. If possible, you could perhaps define a 'wrapper'
function which returns other values in OUT parameters. For example :
FUNCTION UserInfo_P (
oSID OUT SYS.V_$SESSION.SID%TYPE,
oLogin OUT SYS.V_$SESSION.USERNAME%TYPE) RETURN
DISPATCHERS.ROLE_ID%TYPE AS
--
l_rec User_Profile ;
--
BEGIN
l_rec := UserInfo ;
oSID := l_rec.sid ;
oLogin := l_rec.login ;
RETURN l_rec.role ;
END ;
Then in your Perl code, you would do something like this (E&OE) ...
:
my ($sid, $login, $role) ;
my $sth = $dbh->prepare(q(BEGIN :role := UserInfo_P (:sid, :login) ; END
;)) ;
$sth->bind_param_inout (':role', \$role, 256) ; # Set length
appropriately
$sth->bind_param_inout (':sid', \$sid, 10) ;
$sth->bind_param_inout (':login', \$login, 30) ;
$sth->execute ;
#
# As long as the statement executed correctly, $sid, $login and $role
will
# be populated.
#
HTH,
Steve
-----Original Message-----
From: Steve Edwards [mailto:[EMAIL PROTECTED]
Sent: Tuesday, 19 August 2003 11:19 AM
To: <
Subject: Problems using DBD::Oracle 1.14, Returning an Oracle 9i RECORD
datatype
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
