Re: DBD-Oracle stored proc with array bug

Built from SVN, works in our test environment.

John Scoles wrote, on or about 12/18/2010 8:08 PM:
> 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] <mailto:[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]