Thanks Tim and Michael!
Yes, it seems that DBD::Oracle doesn't yet support that type of data
structure. I rewrote the PL/SQL to a more conventional style and switched
to standard prototyping, iterate bind_param() over all 30 fields, everything
works great...
-----Original Message-----
From: Tim Bunce [mailto:[EMAIL PROTECTED]]
Sent: Friday, May 11, 2001 5:24 AM
To: Eric Riggers
Cc: [EMAIL PROTECTED]
Subject: Re: passing an ORA_RSET into an Oracle stored procedure?
On Thu, May 10, 2001 at 04:29:04PM -0700, Eric Riggers wrote:
> Hi,
> I'm trying to call a stored procedure that needs a "record type
paramater".
> I'm unclear as to how to pass the record data. The record data is a hash;
I
> believe i need to pass in just the values of the hash, as a flattened list
> $reg_string.
>
> Something like this?
>
> my $proc = 'proc_name';
> my $reg_string = 'John,Smith,,123 Main Street,,';
>
> my $sth = $dbh->prepare("BEGIN $proc(?,?); END;");
> $sth->bind_param(1, $reg_string, { ora_type => ORA_RSET } );
> $sth->bind_param_inout(2, \$self->{_result}, 100);
>
> Right now this throws the following error:
> Argument "ORA_RSET" isn't numeric in subroutine entry at
lib/Mail/Message.pm
> line 515.
You haven't imported the ORA_RSET definition,
ie 'use DBD::Oracle qw(:ora_types)'.
But that won't help you get much further. I'm fairly sure that DBD::Oracle
doesn't yet support "record type parameters". The best thing to do is
probably to change the PL/SQL used above to something more like:
my $sth = $dbh->prepare(qq{
BEGIN
... declare a record type variable
... initialise it using a placeholder for each field
$proc(name_of_rec_type_var, ...)
END;
});
Tim.
