I may be wrong here, but I don't think DBD::Oracle can cope with PL/SQL
tables or VARRAYs.
Go with REF cursors.
There are examples of passing back cursors from a PL/SQL block in the
DBD:Oracle docs. The principle is the same for REF cursors returned from a
stored procedure. Here's a quick example:
use DBD::Oracle qw/ :ora_types /;
my ($sth1, $sth2);
$sth1 = $dbh->prepare(q{
BEGIN
get_ref_cursor( :cursor );
END;
});
$sth1->bind_param_inout(":cursor", \$sth2, 0, { ora_type => ORA_RSET } );
$sth1->execute();
# $sth2 is now a valid DBI statement handle for the cursor
# - assuming the procedure worked...
while (my @row = $sth2->fetchrow_array()) {
# Your processing here
}
I don't have the procedure source to hand, sorry. You'll have to figure that
bit out yourself ;-)
HTH
Simon
> ----------
> From: Andrey Bronfin
> Sent: Sunday, September 16, 2001 19:09 PM
> To: Dbi-Users@Perl. Org (E-mail);
> [EMAIL PROTECTED]
> Subject: PERL , DBI , Oracle - how to accept output from a PL/SQL
> procedur e that returns multiple rows
>
>
> Dear gurus !
> I'm considering to write a PL/SQL procedure that returns multiple rows
> (most
> likely via REF cursor , although i might think of PL/SQL tables or
> VARRAYs)
> .
> How can i accept such an output in PERL script ?
> Thanks a lot for your help,
> DBAndrey
>
>
>
>
>
***********************************************************************
Confidentiality: This e-mail and its attachments are intended for the
above named recipient(s) only and may be confidential and/or
privileged. If they have come to you in error you must take no action
based on them, nor must you copy or disclose them or any part of their
contents to any person or organisation; please reply to this e-mail
and highlight the error immediately and delete this e-mail and its
attachments from your computer system.
Security Warning: Please note that this e-mail has been created in the
knowledge that Internet e-mail is not a 100% secure communications
medium. We advise that you understand and observe this lack of
security when e-mailing us.
Viruses: Although we have taken steps to ensure that this e-mail and
its attachments are free from any virus, we advise that in keeping
with good computing practice the recipient should ensure they are
actually virus free
***********************************************************************
