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();

[download]

The problem is that if we have around 1000 elements per array the call works beautiful, 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..."

We have tried almost everything in our mind, setting a lot of the specific bind field attributes (e.g. ORA_MAXDATA_SIZE and the like) without success. Any ideas? I believe the number of elements is not the issue here, more concerned about the message total size which might exceed a DBI limitation or so, what do you think? DBI version is 1.607. Thanks!

In reply to DBI Oracle Input Array Binds by hhferreira

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.