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 [EMAIL PROTECTED] To: dbi-users@perl.org 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. 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:[EMAIL PROTECTED] 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 [EMAIL PROTECTED] To: dbi-users@perl.org 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 = join(q{', '}, @input_array);
$sth = $dbh-prepare(END_SQL);
DECLARE
// Not sure how to declare a varray, as I don't use them
my_varray varray;
BEGIN
my_varray := '$varray';
some_proc_call(my_varray);
END
END_SQL
HTH.
Ted Behling
-Original Message-
From: Shuxteau, Kiki [mailto:[EMAIL PROTECTED]
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.
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:[EMAIL PROTECTED]
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 [EMAIL PROTECTED]
To: dbi-users@perl.org
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
