Re: Issues with DBI Oracle Input Array Binds (ORA_VARCHAR2_TABLE)

[Martin J. Evans]

On 24/01/14 10:29, hhferreira wrote:
Hi Guys,
Hope you can provide us some enlightenment!

We have the following code which basically calls an oracle procedure passing as 
inputs 6 bind arrays (converted in ORA_VARCHAR2_TABLE elements) and one string:

    /my $sth = $self->prepare( q{/
    /    begin pkg_abcdef.pr_setAbcdef(/
    /       :in_sourceType,/
    /       :in_sourceNames,/
    /       :in_peerTypes,/
    /       :in_peerNames,/
    /       :in_writables,/
    /       :in_requireLevels,/
    /       :in_testdefs/
    /     );/
    /    end;/
    /} );/
    /
    /
    /$sth->bind_param( ':in_sourceType',    $sourceType, /
    /    { ora_type => ORA_VARCHAR2       } );/
    /$sth->bind_param( ':in_sourceNames',   $sourceNames, /
    /    { ora_type => ORA_VARCHAR2_TABLE } );/
    /$sth->bind_param( ':in_peerTypes',     $peerTypes, /
    /    { ora_type => ORA_VARCHAR2_TABLE } );/
    /$sth->bind_param( ':in_peerNames',     $peerNames, /
    /    { ora_type => ORA_VARCHAR2_TABLE } );/
    /$sth->bind_param( ':in_writables',     $writables, /
    /    { ora_type => ORA_VARCHAR2_TABLE } );/
    /$sth->bind_param( ':in_requireLevels', $requireLevels, /
    /    { ora_type => ORA_VARCHAR2_TABLE } );/
    /$sth->bind_param( ':in_testdefs',      $testDefs, /
    /    { ora_type => ORA_VARCHAR2_TABLE } );/
    /
    /
    /$sth->execute();/


The problem is that if we have around 1000 elements per array the call works 
beautifully, but with for instance 1500 it raises an exception, namely an 
invalid number of bind elements!

    /19:05:57 ERROR: Caught an exception from DB: DBD::Oracle::st execute failed: 
called with 3443804 bind variables when 7 are needed [for Statement..."/
    /
    /

Here is the output of dbi_Trace=15:

<snipped log>

As I answered in perlmonks, that log didn't give me enough info. Perhaps you 
could send me personally all the log.
 
PERL version: 5.12.1
DBI version: 1.611
DBD::Oracle: 1.21

That version of DBD::Oracle is very old - 11th April 2008.

On the other hand your DBI is 19th April 2010.

I'd rather not debug the issue on versions so old. Is it at all possible you 
can try the latest versions so we can at least see if the problem is already 
fixed? You can do this without actually installing (overwriting) your existing 
versions (ask if you are unsure how to do this).


We have made a standalone script and it works perfectly even with 20000 entries 
per array. In our application we are using perl objects all over the code, can 
this be a memory leak somewhere else in the code?!

Shame, as this would definitely be the best way to go. It would be worth 
putting a bit more effort into this.

Any help would be highly appreciated!

Thanks in advance.

Best Regards,
Hélder Hugo Ferreira


Martin