On 24/01/14 15:03, John Scoles wrote:
I will try and have a quick look at it.
I would go with perlbrew http://perlbrew.pl/ as you can have many differnt
versions of the mods/perl and test them all at once.
The problem with perlbrew is Hélder will have to install all the modules his
app needs in addition to the new DBI and DBD::Oracle and he'll probably end up
with loads of other newer modules thus potentially moving the problem.
PERL5LIB setting or:
download and build DBI in dir1 and download and build DBD::Oracle in dir2 then
run your app with
perl -Idir1/blib/lib -Idir1/blib/arch -Idir2/blib/lib -Idir2/blib/arch myapp.pl
Martin
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--
Date: Fri, 24 Jan 2014 14:59:25 +0000
Subject: Re: Issues with DBI Oracle Input Array Binds (ORA_VARCHAR2_TABLE)
From: hhferre...@gmail.com
To: martin.ev...@easysoft.com
CC: byter...@hotmail.com; boh...@ntlworld.com; dbi-users@perl.org
Hi Guys,
We have made the dump of the contents of /$sth->{ParamValues} /into the
attached file which basically contains all values set for the 6 input binds
(ora_varchar2_table elements actually).
We will proceed with the test environment setup using the latest perl and
module versions, Martin mentioned this could be done without actually
installing (overwriting) our existing versions, I suppose you are referring to
those environment variables such like PERL5LIB which we can tweak to use the
right versions right? Or there is a better approach? Our working environment is
rather complex (big company overweight) and that task can take a while although
seems simple to perform so if there is a simple way do let us know :-)
Thanks for your hints!
Best Regards,
Hélder Hugo Ferreira
On Fri, Jan 24, 2014 at 1:28 PM, Martin J. Evans <martin.ev...@easysoft.com
<mailto:martin.ev...@easysoft.com>> wrote:
On 24/01/14 12:26, hhferreira wrote:
Hi,
We have already tried using ora_maxarray_numentries and other similar
attributes unsuccessfully.
Martin found that immediately before the error the following message is
written:
{{ execute callback CODE(0xb832be8) being invoked
However we have done a dbi_trace with 1000 elements in the arrays
(which works!) to see whether a similar message is logged and it is, so I would
not go into that direction though. Will setup a test environment using the
latest DBD and DBI versions to see whether the leaks in DBD are causing this
behavior.
Thanks.
Best Regards,
Hélder Hugo Ferreira
The reason I pointed out the execute callback is that it is only called
just before the failure and we cannot see from the trace what code is in it. If
we cannot see the code who knows what it is doing?
1 -> FETCH for DBD::Oracle::st (DBI::st=HASH(0xd077218)~INNER
'ParamValues') thr#8916008
1 <- FETCH= ( HASH(0xd0758e8)7keys ) [1 items] at /home/
Might have been interesting if we knew what was in it.
Perhaps you could get ParamValues just before execute and if execute fails
catch it and Dumper them.
use Data::Dumper;
.
.
my $pv = $sth->{ParamValues};
eval {
$sth->execute;
};
if (my $ev = $@) {
print Dumper($pv);
die $ev;
}
However, I still think testing the latest DBI/DBD::Oracle is the best thing
to do first.
Martin
On Fri, Jan 24, 2014 at 12:09 PM, John Scoles <byter...@hotmail.com
<mailto:byter...@hotmail.com> <mailto:byter...@hotmail.com
<mailto:byter...@hotmail.com>>> wrote:
As Martin said that is rather old version of DBD only 3 since
native exe_array was introduced 1.18, and I rember there being some leaks in
early version of the native exe_array.
If you can upgrade you DBD.
Yyou might try to set the 'ora_maxarray_numentries' on you binds
as well as that works on the Oracle side of things to limit memory.
As it runs stands alone as you say it might be that the things you
are binnding are not being released by perl as a referace to them may still
exist.
Cheers
John
> Date: Fri, 24 Jan 2014 10:53:54 +0000
> From: boh...@ntlworld.com <mailto:boh...@ntlworld.com>
<mailto:boh...@ntlworld.com <mailto:boh...@ntlworld.com>>
> To: hhferre...@gmail.com <mailto:hhferre...@gmail.com> <mailto:hhferre...@gmail.com
<mailto:hhferre...@gmail.com>>; dbi-users@perl.org <mailto:dbi-users@perl.org>
<mailto:dbi-users@perl.org <mailto:dbi-users@perl.org>>
> Subject: Re: Issues with DBI Oracle Input Array Binds
(ORA_VARCHAR2_TABLE)
>
> 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
