Re: DBD-Oracle stored proc with array bug

Ok I got it to work 100% with the test seems you gave a little too much SP

You will find it in trunk of DBD::Oracle

which is here

http://svn.perl.org/modules/dbd-oracle/trunk


Thanks again

Cheers
John Scoles

On Tue, Dec 14, 2010 at 10:39 AM, Tim Oertel <[email protected]> wrote:

>
> Hi,
>
> We were recently trying to pass an array to a stored procedure.
> It worked fine the first call, but subsequent calls were receiving
> the wrong array size, and with old array entries.
>
> After some investigation, it looks like once an array parameter
> is used with a given size array, it ignores any future array length.
>
> i.e.
>
> @array_of_something = (1, 2, 3, 4, 5);
>
> $sth->bind_param(1, \@array_of_something, {
>   ora_type => ORA_NUMBER_TABLE,
>   ora_internal_type => SQLT_INT });
> $sth->execute();
>
> @array_of_something = (6, 7, 8);
>
> $sth->bind_param(1, \@array_of_something, {
>   ora_type => ORA_NUMBER_TABLE,
>   ora_internal_type => SQLT_INT });
> $sth->execute();
>
> ...doesn't do what is expected.  The stored proceedure
> receives an array of (6, 7, 8, 4, 5), in the second execute().
>
>
> In the process of debugging that, we grabbed newer versions of DBD::Oracle,
> to check if a more recent version had a fix, and lacking that, creating
> a patch against svn.
>
> Turned out we ran into a second bug, our dev system has recent Oracle
> libraries on it, but our db is Oracle 9.2, and (after investigation) it
> looks like there is a known issue with Oracle 10.2 libs, doing a ping
> against 9.2, which causes a listener crash (I think).
>
> The fix for that was to revert to the server version check if the server
> is too old.
>
>
> So, I've attached a patch, plus a test case for the first problem.
> The patch is a patch against svn, 14564.  It was tested on a Linux
> box, with 10.2.0.3 client libs and 9.2.0.8.0 server, also running on
> Linux.
>
>
> If I've screwed up formatting, or need to do this differently, let me
> know, and I'll try to fix it.  If the fixes are wrong, I'm all ears
> on that too.
>
>
> --
> Tim Oertel       |"Why should I be content to simply
> VP Engineering   | live in this world, when I, as a
> Ashergroup, Inc. | human being, can CREATE it?!"
> 585-586-0020     | IM via google/XMPP: [email protected]
>

--
From the Pythian family to yours, Happy Holidays and all the best in 2011!