Just so everyone knows, I pointed hhferreira here in the following thread on
perl monks:
https://perlmonks.org/?node_id=1071794
Martin
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:
/.../
/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
