bind varray as in or inout param

------_=_NextPart_001_01C62105.804556FA
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

Hi,

=20

Can I pass in a VARRAY data type to an oracle stored procedure via perl
or receive VARRAY from Oracle SP?

=20

Thanks,

=20

KS


------_=_NextPart_001_01C62105.804556FA--

Re: bind varray as in or inout param

I have searched around a bit and have no real answer for you. A much
earlier post (2004) on this list said

"You have probably worked this out by now, but what you are trying to do
is not directly possible with the current version of DBD::Oracle. If
you are returning a VARRAY, there are some ways you may be able to work
around this (e.g. returning ref cursor, pipelined function, etc - see
DBD::Oracle docs), but if you are trying to pass in a VARRAY, AFAIK
there's no real solution. "
I still do not think it can do it but I will do a little more digging. I do
remember reading that the varray is penceled in for version 6 of perl so
perhaps you might have long to wait.









----- Original Message -----
From: "Shuxteau, Kiki"
To:
Sent: Tuesday, January 24, 2006 11:44 AM
Subject: bind varray as in or inout param


Hi,



Can I pass in a VARRAY data type to an oracle stored procedure via perl
or receive VARRAY from Oracle SP?



Thanks,



KS

RE: bind varray as in or inout param

Thanks. I came across the same posting and I thought since 2 years had
gone by, things might have changed and varray is supported now.=20

In general, I think if I want to output a varray, I would use refcursor
as a work around. But if I want to input a varray, say, I want to avoid
calling a SP from within in loop inside perl, I don't really know how to
get around that.

Thanks,

Kiki

-----Original Message-----
From: John Scoles [mailto:scoles@pythian.com]=20
Sent: Tuesday, January 24, 2006 12:20 PM
To: Shuxteau, Kiki; dbi-users@perl.org
Subject: Re: bind varray as in or inout param

I have searched around a bit and have no real answer for you. A much
earlier post (2004) on this list said

"You have probably worked this out by now, but what you are trying to do
is not directly possible with the current version of DBD::Oracle. If
you are returning a VARRAY, there are some ways you may be able to work
around this (e.g. returning ref cursor, pipelined function, etc - see
DBD::Oracle docs), but if you are trying to pass in a VARRAY, AFAIK
there's no real solution. "
I still do not think it can do it but I will do a little more digging. I
do
remember reading that the varray is penceled in for version 6 of perl so
perhaps you might have long to wait.









----- Original Message -----=20
From: "Shuxteau, Kiki"
To:
Sent: Tuesday, January 24, 2006 11:44 AM
Subject: bind varray as in or inout param


Hi,



Can I pass in a VARRAY data type to an oracle stored procedure via perl
or receive VARRAY from Oracle SP?



Thanks,



KS

RE: bind varray as in or inout param

One workaround, although somewhat inelegant, would be to turn your array
into a Perl string, then interpolate the string into an anonymous PL/SQL
block which invokes the procedure. You could use something like the
following half-pseudocode:

--------
$varray =3D join(q{', '}, @input_array);

$sth =3D $dbh->prepare(< DECLARE
// Not sure how to declare a varray, as I don't use them
my_varray varray;
BEGIN
my_varray :=3D '$varray';
some_proc_call(my_varray);
END
END_SQL
--------

HTH.

Ted Behling

-----Original Message-----
From: Shuxteau, Kiki [mailto:kiki.shuxteau@Vanderbilt.Edu]=20
Sent: Tuesday, January 24, 2006 2:47 PM
To: John Scoles; dbi-users@perl.org
Subject: RE: bind varray as in or inout param

Thanks. I came across the same posting and I thought since 2 years had
gone by, things might have changed and varray is supported now.=20

In general, I think if I want to output a varray, I would use refcursor
as a work around. But if I want to input a varray, say, I want to avoid
calling a SP from within in loop inside perl, I don't really know how to
get around that.

Thanks,

Kiki

-----Original Message-----
From: John Scoles [mailto:scoles@pythian.com]
Sent: Tuesday, January 24, 2006 12:20 PM
To: Shuxteau, Kiki; dbi-users@perl.org
Subject: Re: bind varray as in or inout param

I have searched around a bit and have no real answer for you. A much
earlier post (2004) on this list said

"You have probably worked this out by now, but what you are trying to do
is not directly possible with the current version of DBD::Oracle. If you
are returning a VARRAY, there are some ways you may be able to work
around this (e.g. returning ref cursor, pipelined function, etc - see
DBD::Oracle docs), but if you are trying to pass in a VARRAY, AFAIK
there's no real solution. "
I still do not think it can do it but I will do a little more digging. I
do remember reading that the varray is penceled in for version 6 of perl
so perhaps you might have long to wait.









----- Original Message -----
From: "Shuxteau, Kiki"
To:
Sent: Tuesday, January 24, 2006 11:44 AM
Subject: bind varray as in or inout param


Hi,



Can I pass in a VARRAY data type to an oracle stored procedure via perl
or receive VARRAY from Oracle SP?



Thanks,



KS