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:
*...*
*bd_rebind_ph_varchar2_table(): Copying length=1 array[1558]='N'.
dbd_rebind_ph_varchar2_table(): Copying length=1 array[1559]='N'.
dbd_rebind_ph_varchar2_table(): Copying length=1 array[1560]='N'.
dbd_rebind_ph_varchar2_table(): Copying length=1 array[1561]='N'.
OCIBindByName(b877a34,c2e3b5c,b8724c8,":in_testdefs",placeh_len=12,value_p=dd5d418,value_sz=2,dty=5,indp=dcaf908,alenp=dbc6708,rcodep=0,maxarr_len=1562,curelep=c2e3b90
(*=1562),mode=DEFAULT,0)=SUCCESS
OCIOCIBindArrayOfStruct(b90a14c,b8724c8,2,2,2,0)=SUCCESS
OCIAttrGet(b90a14c,OCI_HTYPE_BIND,c2e3b48,0,31,b8724c8)=SUCCESS
dbd_rebind_ph_varchar2_table(): bind :in_testdefs <==
ARRAY(0xdc7adc0) (in, not-utf8, csid 1->0->1, ftype 201, csform 0
(0)->0 (0), maxlen 2, maxdata_size 0)
OCIAttrSet(b90a14c,OCI_HTYPE_BIND,
ffa5e998,0,Attr=31,b8724c8)=SUCCESS
rebind :in_testdefs done with ftype 201
(ORA_VARCHAR2_TABLE)
<- bind_param= ( 1 ) [1 items] at
/mycode/start/..ComponentSet.pm line 400 via at
/mycode/start/..ComponentSet.pm line 877
{{ execute callback CODE(0xb832be8) being invoked
1 -> FETCH for DBD::Oracle::st (DBI::st=HASH(0xd077218)~INNER
'ParamValues') thr#8916008
1 <- FETCH= ( HASH(0xd0758e8)7keys ) [1 items] at
/mycode/start/..DBConn.pm line 301 via at
/mycode/start/..ComponentSet.pm line 402
}} execute callback CODE(0xb832be8) returned
-> execute for DBD::Oracle::st
(DBI::st=HASH(0xd39bbf0)~0xd077218) thr#8916008
!! ERROR: -1 'called with 11541898 bind variables when 7 are
needed' (err#0)
<- execute= ( undef ) [1 items] at
/mycode/start/..ComponentSet.pm line 402 via at
/mycode/start/..ComponentSet.pm line 877
>> FETCH DISPATCH (DBI::st=HASH(0xd077218) rc2/1 @2 g2
ima404 pid#30775) at /mycode/start/..ComponentSet.pm line 402 via at
/mycode/start/..ComponentSet.pm line 877
1 -> FETCH for DBD::Oracle::st (DBI::st=HASH(0xd077218)~INNER
'ParamValues') thr#8916008
ERROR: -1 'called with 11541898 bind variables when 7 are
needed' (err#0)
1 <- FETCH= ( HASH(0xd39f058)7keys ) [1 items] at
/mycode/start/..ComponentSet.pm line 402 via at
/mycode/start/..ComponentSet.pm line 877
-> HandleError on DBI::st=HASH(0xd077218) via CODE(0xb832ab8) (undef)
>> can DISPATCH (DBI::st=HASH(0xd39bbf0) rc1/6 @2 g2
ima100 pid#30775) at /mycode/start/..Dumper/HumanReadable.pm line 130
via at /mycode/start/..Dumper/HumanReadable.pm line 259
<- can(dump) = 0 (? 0)
>> can DISPATCH (DBI::st=HASH(0xd39bbf0) rc1/6 @2 g2
ima100 pid#30775) at /mycode/start/..Dumper/HumanReadable.pm line 130
via at /mycode/start/..Dumper/HumanReadable.pm line 259
<- can(dumpKeyNames) = 0 (? 0)
>> can DISPATCH (DBI::st=HASH(0xd39bbf0) rc1/6 @2 g2
ima100 pid#30775) at /mycode/start/..Dumper/HumanReadable.pm line 130
via at /mycode/start/..Dumper/HumanReadable.pm line 259
<- can(dumpKeyStyles) = 0 (? 0)
>> can DISPATCH (DBI::st=HASH(0xd39bbf0) rc1/6 @2 g2
ima100 pid#30775) at /mycode/start/..Dumper/HumanReadable.pm line 130
via at /mycode/start/..Dumper/HumanReadable.pm line 259
<- can(dumpKeyComments) = 0 (? 0)
>> can DISPATCH (DBI::st=HASH(0xd39bbf0) rc1/6 @2 g2
ima100 pid#30775) at /mycode/start/..Dumper/HumanReadable.pm line 130
via at /mycode/start/..Dumper/HumanReadable.pm line 259
<- can(asString) = 0 (? 0)
>> FIRSTKEY DISPATCH (DBI::st=HASH(0xd077218) rc2/1 @1 g2
ima4 pid#30775) at /mycode/start/..Dumper/HumanReadable.pm line 234
via at /mycode/start/..Dumper/HumanReadable.pm line 130
1 -> FIRSTKEY in DBD::_::common for DBD::Oracle::st
(DBI::st=HASH(0xd077218)~INNER) thr#8916008
ERROR: -1 'called with 11541898 bind variables when 7 are
needed' (err#0)
1 <- FIRSTKEY= ( undef ) [1 items] at
/mycode/start/..Dumper/HumanReadable.pm line 234 via at
/mycode/start/..Dumper/HumanReadable.pm line 130
>> FETCH DISPATCH (DBI::st=HASH(0xd077218) rc3/1 @2 g2
ima404 pid#30775) at /mycode/start/..DBConn.pm line 365 via at
/mycode/start/..DBConn.pm line 203
1 -> FETCH for DBD::Oracle::st (DBI::st=HASH(0xd077218)~INNER
'RaiseError') thr#8916008
.. FETCH DBI::st=HASH(0xd077218) 'RaiseError' = 1
ERROR: -1 'called with 11541898 bind variables when 7 are
needed' (err#0)
1 <- FETCH= ( 1 ) [1 items] at /mycode/start/..DBConn.pm line 365
via at /mycode/start/..DBConn.pm line 203
-> DESTROY for DBD::Oracle::st (DBI::st=HASH(0xd077218)~INNER)
thr#8916008
dbd_st_destroy
OCIHandleFree(b877a34,OCI_HTYPE_STMT)=SUCCESS
ERROR: -1 'called with 11541898 bind variables when 7 are
needed' (err#0)
<- DESTROY= ( undef ) [1 items] at
/mycode/start/../cpan/lib/Try/Tiny.pm line 90 via at
/mycode/start/../cpan/lib/Try/Tiny.pm line 90*
PERL version: 5.12.1
DBI version: 1.611
DBD::Oracle: 1.21
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?!
Any help would be highly appreciated!
Thanks in advance.
Best Regards,
Hélder Hugo Ferreira
