RE: Issues with DBI Oracle Input Array Binds (ORA_VARCHAR2_TABLE)
From: byter...@hotmail.com
To: tim.bu...@pobox.com
CC: martin.ev...@easysoft.com; hhferre...@gmail.com; dbi-users@perl.org
Subject: RE: Issues with DBI Oracle Input Array Binds (ORA_VARCHAR2_TABLE)
Date: Fri, 31 Jan 2014 18:04:44 -0500
Opp hit send too fast
> Date: Fri, 31 Jan 2014 22:09:02 +
> From: tim.bu...@pobox.com
> To: byter...@hotmail.com
> CC: martin.ev...@easysoft.com; tim.bu...@pobox.com; hhferre...@gmail.com;
> boh...@ntlworld.com; dbi-users@perl.org
> Subject: Re: Issues with DBI Oracle Input Array Binds (ORA_VARCHAR2_TABLE)
>
> On Fri, Jan 31, 2014 at 12:50:36PM -0500, John Scoles wrote:
> > Well I did do some testing. The leak was very small (1k over 10 min run)
> > but only when one does
> > $shift->FETCH( 'ParamValues' ),
> > in the child callback.
>
With of course the older DBI <1.63,
I did not see a leak with 1.63 no matter how long I let it run
> If it doesn't keep growing with more call then it's not a leak.
>
> > Tim what would the impact of the above?? I know before 1.63 this
> > $shift->{ParamValues'},
> > gave you undef which is why the WTF comment was there.
>
> Because the inner handle is a plain blessed hash ref, whereas the outer
> handle is *tied* blessed hash ref.
>
> There's no 'ParamValues' key in that hash, so you get an undef.
>
> The ParamValues lookup is handled by the FETCH method call.
>
> > Why if in the CB we had the outter handle would the FETCH give you the
> > attributes of the Inner handle??
>
> Calling $outer->{ParamValues} in a tied hash ref triggers a call to
> $outer->FETCH('ParamValues') which then gets dispatched by the DBI to
> $inner->FETCH('ParamValues') which does the work.
>
In pre 1.63 in the callback it would be like this;
Calling $inner->{ParamValues} = undef;
calling $inner->FETCH('ParamValues') which does the work.
OK
>
$inner->{ParamValues}
> For more details see http://perldoc.perl.org/perltie.html
>
> > Just a silly question?
>
> No such thing :)
>
> Tim.
>
> > Cheers
> >
> >
> >
> > > Date: Fri, 31 Jan 2014 17:00:20 +
> > > From: martin.ev...@easysoft.com
> > > To: tim.bu...@pobox.com; byter...@hotmail.com
> > > CC: hhferre...@gmail.com; boh...@ntlworld.com; dbi-users@perl.org
> > > Subject: Re: Issues with DBI Oracle Input Array Binds (ORA_VARCHAR2_TABLE)
> > >
> > > On 31/01/14 16:21, Tim Bunce wrote:
> > > > On Fri, Jan 31, 2014 at 09:11:28AM -0500, John Scoles wrote:
> > > >> A final note on this.
> > > >>
> > > >> Seems there was a very very long unknown bug in DBI which was only fix
> > > >> a few days ago wiht DB
> > 1.6.31
> > > >
> > > > If you mean Callbacks getting an inner handle, that wasn't a bug as
> > > > such.
> > > > More like a design choice that proved non-optimal.
> > > >
> > > >> [1]http://blogs.perl.org/mt/mt.fcgi?__mode=view&_type=entry&id=5570&blog_id=2165
> > > >
> > > > That's
> > > > http://blogs.perl.org/users/byterock/2014/01/callbacks-ate-my-brain.html
> > > > I presume.
> > > >
> > > >> The end result of this bug was that when callbacks are used on the
> > > >> statement handle some attributes will not be there so you
> > > >> programmer who did this
> > > >>
> > > >> $sth->FETCH( 'ParamValues' ), # WTF? - returns a reference to an array
> > > >> of hashes
> > > >>
> > > >> was most likely complaing that the
> > > >>
> > > >> $sth->{ParamValues},
> > > >>
> > > >> should return a ref but was just returning undef.
> > > >>
> > > >> So he 'Kludged' the code to get the value directly with the FETCH
> > > >> which works
> > > >
> > > > I'm not sure what you're saying here John. Using
> > > > $sth->FETCH('ParamValues')
> > > > is perfectly reasonable. It was required before 1.631 and optional with
> > > > 1.631+ now that $h->{ParamValues} works.
> > > >
> > > >> sort of, but it does bleed memory every so slighly.
> > > >
> > > > Are you sure? This is the first I've heard of such a leak.
> > > >
> > > > Tim.
> > >
> > > I've found no evidence of a
RE: Issues with DBI Oracle Input Array Binds (ORA_VARCHAR2_TABLE)
> Date: Fri, 31 Jan 2014 22:09:02 +
> From: tim.bu...@pobox.com
> To: byter...@hotmail.com
> CC: martin.ev...@easysoft.com; tim.bu...@pobox.com; hhferre...@gmail.com;
> boh...@ntlworld.com; dbi-users@perl.org
> Subject: Re: Issues with DBI Oracle Input Array Binds (ORA_VARCHAR2_TABLE)
>
> On Fri, Jan 31, 2014 at 12:50:36PM -0500, John Scoles wrote:
> > Well I did do some testing. The leak was very small (1k over 10 min run)
> > but only when one does
> > $shift->FETCH( 'ParamValues' ),
> > in the child callback.
>
> If it doesn't keep growing with more call then it's not a leak.
>
> > Tim what would the impact of the above?? I know before 1.63 this
> > $shift->{ParamValues'},
> > gave you undef which is why the WTF comment was there.
>
> Because the inner handle is a plain blessed hash ref, whereas the outer
> handle is *tied* blessed hash ref.
>
> There's no 'ParamValues' key in that hash, so you get an undef.
>
> The ParamValues lookup is handled by the FETCH method call.
>
> > Why if in the CB we had the outter handle would the FETCH give you the
> > attributes of the Inner handle??
>
> Calling $outer->{ParamValues} in a tied hash ref triggers a call to
> $outer->FETCH('ParamValues') which then gets dispatched by the DBI to
> $inner->FETCH('ParamValues') which does the work.
>
> For more details see http://perldoc.perl.org/perltie.html
>
> > Just a silly question?
>
> No such thing :)
>
> Tim.
>
> > Cheers
> >
> >
> >
> > > Date: Fri, 31 Jan 2014 17:00:20 +
> > > From: martin.ev...@easysoft.com
> > > To: tim.bu...@pobox.com; byter...@hotmail.com
> > > CC: hhferre...@gmail.com; boh...@ntlworld.com; dbi-users@perl.org
> > > Subject: Re: Issues with DBI Oracle Input Array Binds (ORA_VARCHAR2_TABLE)
> > >
> > > On 31/01/14 16:21, Tim Bunce wrote:
> > > > On Fri, Jan 31, 2014 at 09:11:28AM -0500, John Scoles wrote:
> > > >> A final note on this.
> > > >>
> > > >> Seems there was a very very long unknown bug in DBI which was only fix
> > > >> a few days ago wiht DB
> > 1.6.31
> > > >
> > > > If you mean Callbacks getting an inner handle, that wasn't a bug as
> > > > such.
> > > > More like a design choice that proved non-optimal.
> > > >
> > > >> [1]http://blogs.perl.org/mt/mt.fcgi?__mode=view&_type=entry&id=5570&blog_id=2165
> > > >
> > > > That's
> > > > http://blogs.perl.org/users/byterock/2014/01/callbacks-ate-my-brain.html
> > > > I presume.
> > > >
> > > >> The end result of this bug was that when callbacks are used on the
> > > >> statement handle some attributes will not be there so you
> > > >> programmer who did this
> > > >>
> > > >> $sth->FETCH( 'ParamValues' ), # WTF? - returns a reference to an array
> > > >> of hashes
> > > >>
> > > >> was most likely complaing that the
> > > >>
> > > >> $sth->{ParamValues},
> > > >>
> > > >> should return a ref but was just returning undef.
> > > >>
> > > >> So he 'Kludged' the code to get the value directly with the FETCH
> > > >> which works
> > > >
> > > > I'm not sure what you're saying here John. Using
> > > > $sth->FETCH('ParamValues')
> > > > is perfectly reasonable. It was required before 1.631 and optional with
> > > > 1.631+ now that $h->{ParamValues} works.
> > > >
> > > >> sort of, but it does bleed memory every so slighly.
> > > >
> > > > Are you sure? This is the first I've heard of such a leak.
> > > >
> > > > Tim.
> > >
> > > I've found no evidence of a memory leak with a simple test calling
> > > ParamValues a lot with some
> > parameters. However, I'm not using ORA_VARCHAR2_TABLE. The code is:
> > >
> > > else if (kl==11 && strEQ(key, "ParamValues")) {
> > > HV *pvhv = newHV();
> > > if (imp_sth->all_params_hv) {
> > > SV *sv;
> > > char *key;
> > > I32 keylen;
> > > hv_iterinit(imp_sth->all_params_hv);
> > > while ( (sv = hv_iternextsv(imp_sth->all_params_hv, &key, &keylen)) ) {
> > > phs_t *phs = (phs_t*)(void*)SvPVX(sv); /* placeholder struct */
> > > (void)hv_store(pvhv, key, keylen, newSVsv(phs->sv), 0);
> > > }
> > > }
> > > retsv = newRV_noinc((SV*)pvhv);
> > > cacheit = FALSE;
> > >
> > > }
> > >
> > > which looks sane to me right now. ORA_VARCHAR2_TABLE seems to do strange
> > > things with parameters I
> > don't quite get right now.
> > >
> > > As I said previously to H�lder and John (some of the discussion was off
> > > dbi-users list presumably
> > because it contained log data), although I accept taking the call to
> > ParamValues out has on this
> > occasion made the problem go away I don't understand why. I think there is
> > more to this than it so far
> > looks but without a way of reproducing it myself I won't be spending any
> > more time on it. If it is
> > reproducible in a standalone script I will happily look again.
> > >
> > > Martin
Re: Issues with DBI Oracle Input Array Binds (ORA_VARCHAR2_TABLE)
On Fri, Jan 31, 2014 at 12:50:36PM -0500, John Scoles wrote:
> Well I did do some testing. The leak was very small (1k over 10 min run) but
> only when one does
>$shift->FETCH( 'ParamValues' ),
> in the child callback.
If it doesn't keep growing with more call then it's not a leak.
>Tim what would the impact of the above?? I know before 1.63 this
>$shift->{ParamValues'},
>gave you undef which is why the WTF comment was there.
Because the inner handle is a plain blessed hash ref, whereas the outer
handle is *tied* blessed hash ref.
There's no 'ParamValues' key in that hash, so you get an undef.
The ParamValues lookup is handled by the FETCH method call.
>Why if in the CB we had the outter handle would the FETCH give you the
> attributes of the Inner handle??
Calling $outer->{ParamValues} in a tied hash ref triggers a call to
$outer->FETCH('ParamValues') which then gets dispatched by the DBI to
$inner->FETCH('ParamValues') which does the work.
For more details see http://perldoc.perl.org/perltie.html
>Just a silly question?
No such thing :)
Tim.
>Cheers
>
>
>
>> Date: Fri, 31 Jan 2014 17:00:20 +
>> From: martin.ev...@easysoft.com
>> To: tim.bu...@pobox.com; byter...@hotmail.com
>> CC: hhferre...@gmail.com; boh...@ntlworld.com; dbi-users@perl.org
>> Subject: Re: Issues with DBI Oracle Input Array Binds
> (ORA_VARCHAR2_TABLE)
>>
>> On 31/01/14 16:21, Tim Bunce wrote:
>> > On Fri, Jan 31, 2014 at 09:11:28AM -0500, John Scoles wrote:
>> >> A final note on this.
>> >>
>> >> Seems there was a very very long unknown bug in DBI which was only
> fix a few days ago wiht DB
>1.6.31
>> >
>> > If you mean Callbacks getting an inner handle, that wasn't a bug as
> such.
>> > More like a design choice that proved non-optimal.
>> >
>> >>
> [1]http://blogs.perl.org/mt/mt.fcgi?__mode=view&_type=entry&id=5570&blog_id=2165
>> >
>> > That's
> http://blogs.perl.org/users/byterock/2014/01/callbacks-ate-my-brain.html
>> > I presume.
>> >
>> >> The end result of this bug was that when callbacks are used on the
>> >> statement handle some attributes will not be there so you
>> >> programmer who did this
>> >>
>> >> $sth->FETCH( 'ParamValues' ), # WTF? - returns a reference to an
> array of hashes
>> >>
>> >> was most likely complaing that the
>> >>
>> >> $sth->{ParamValues},
>> >>
>> >> should return a ref but was just returning undef.
>> >>
>> >> So he 'Kludged' the code to get the value directly with the FETCH
> which works
>> >
>> > I'm not sure what you're saying here John. Using
> $sth->FETCH('ParamValues')
>> > is perfectly reasonable. It was required before 1.631 and optional with
>> > 1.631+ now that $h->{ParamValues} works.
>> >
>> >> sort of, but it does bleed memory every so slighly.
>> >
>> > Are you sure? This is the first I've heard of such a leak.
>> >
>> > Tim.
>>
>> I've found no evidence of a memory leak with a simple test calling
> ParamValues a lot with some
>parameters. However, I'm not using ORA_VARCHAR2_TABLE. The code is:
>>
>> else if (kl==11 && strEQ(key, "ParamValues")) {
>> HV *pvhv = newHV();
>> if (imp_sth->all_params_hv) {
>> SV *sv;
>> char *key;
>> I32 keylen;
>> hv_iterinit(imp_sth->all_params_hv);
>> while ( (sv = hv_iternextsv(imp_sth->all_params_hv, &key, &keylen)) ) {
>> phs_t *phs = (phs_t*)(void*)SvPVX(sv); /* placeholder struct */
>> (void)hv_store(pvhv, key, keylen, newSVsv(phs->sv), 0);
>> }
>> }
>> retsv = newRV_noinc((SV*)pvhv);
>> cacheit = FALSE;
>>
>> }
>>
>> which looks sane to me right now. ORA_VARCHAR2_TABLE seems to do strange
> things with parameters I
>don't quite get right now.
>>
>> As I said previously to H�lder and John (some of the discussion was off
> dbi-users list presumably
>because it contained log data), although I accept taking the call to
> ParamValues out has on this
>occasion made the problem go away I don't understand why. I think there is
> more to this than it so far
>looks but without a way of reproducing it myself I won't be spending any
> more time on it. If it is
>reproducible in a standalone script I will happily look again.
>>
>> Martin
RE: Issues with DBI Oracle Input Array Binds (ORA_VARCHAR2_TABLE)
Well I did do some testing. The leak was very small (1k over 10 min run) but
only when one does
$shift->FETCH( 'ParamValues' ),
in the child callback.
Tim what would the impact of the above?? I know before 1.63 this
$shift->{ParamValues'},
gave you undef which is why the WTF comment was there.
Why if in the CB we had the outter handle would the FETCH give you the
attributes of the Inner handle??
Just a silly question?
Cheers
> Date: Fri, 31 Jan 2014 17:00:20 +
> From: martin.ev...@easysoft.com
> To: tim.bu...@pobox.com; byter...@hotmail.com
> CC: hhferre...@gmail.com; boh...@ntlworld.com; dbi-users@perl.org
> Subject: Re: Issues with DBI Oracle Input Array Binds (ORA_VARCHAR2_TABLE)
>
> On 31/01/14 16:21, Tim Bunce wrote:
> > On Fri, Jan 31, 2014 at 09:11:28AM -0500, John Scoles wrote:
> >> A final note on this.
> >>
> >> Seems there was a very very long unknown bug in DBI which was only fix a
> >> few days ago wiht DB 1.6.31
> >
> > If you mean Callbacks getting an inner handle, that wasn't a bug as such.
> > More like a design choice that proved non-optimal.
> >
> >> [1]http://blogs.perl.org/mt/mt.fcgi?__mode=view&_type=entry&id=5570&blog_id=2165
> >
> > That's
> > http://blogs.perl.org/users/byterock/2014/01/callbacks-ate-my-brain.html
> > I presume.
> >
> >> The end result of this bug was that when callbacks are used on the
> >> statement handle some attributes will not be there so you
> >> programmer who did this
> >>
> >> $sth->FETCH( 'ParamValues' ), # WTF? - returns a reference to an array of
> >> hashes
> >>
> >> was most likely complaing that the
> >>
> >> $sth->{ParamValues},
> >>
> >> should return a ref but was just returning undef.
> >>
> >> So he 'Kludged' the code to get the value directly with the FETCH which
> >> works
> >
> > I'm not sure what you're saying here John. Using $sth->FETCH('ParamValues')
> > is perfectly reasonable. It was required before 1.631 and optional with
> > 1.631+ now that $h->{ParamValues} works.
> >
> >> sort of, but it does bleed memory every so slighly.
> >
> > Are you sure? This is the first I've heard of such a leak.
> >
> > Tim.
>
> I've found no evidence of a memory leak with a simple test calling
> ParamValues a lot with some parameters. However, I'm not using
> ORA_VARCHAR2_TABLE. The code is:
>
> else if (kl==11 && strEQ(key, "ParamValues")) {
> HV *pvhv = newHV();
> if (imp_sth->all_params_hv) {
> SV *sv;
> char *key;
> I32 keylen;
> hv_iterinit(imp_sth->all_params_hv);
> while ( (sv = hv_iternextsv(imp_sth->all_params_hv, &key, &keylen)) ) {
> phs_t *phs = (phs_t*)(void*)SvPVX(sv); /* placeholder struct */
> (void)hv_store(pvhv, key, keylen, newSVsv(phs->sv), 0);
> }
> }
> retsv = newRV_noinc((SV*)pvhv);
> cacheit = FALSE;
>
> }
>
> which looks sane to me right now. ORA_VARCHAR2_TABLE seems to do strange
> things with parameters I don't quite get right now.
>
> As I said previously to Hélder and John (some of the discussion was off
> dbi-users list presumably because it contained log data), although I accept
> taking the call to ParamValues out has on this occasion made the problem go
> away I don't understand why. I think there is more to this than it so far
> looks but without a way of reproducing it myself I won't be spending any more
> time on it. If it is reproducible in a standalone script I will happily look
> again.
>
> Martin
Re: Issues with DBI Oracle Input Array Binds (ORA_VARCHAR2_TABLE)
On 31/01/14 16:21, Tim Bunce wrote:
On Fri, Jan 31, 2014 at 09:11:28AM -0500, John Scoles wrote:
A final note on this.
Seems there was a very very long unknown bug in DBI which was only fix a
few days ago wiht DB 1.6.31
If you mean Callbacks getting an inner handle, that wasn't a bug as such.
More like a design choice that proved non-optimal.
[1]http://blogs.perl.org/mt/mt.fcgi?__mode=view&_type=entry&id=5570&blog_id=2165
That's http://blogs.perl.org/users/byterock/2014/01/callbacks-ate-my-brain.html
I presume.
The end result of this bug was that when callbacks are used on the
statement handle some attributes will not be there so you
programmer who did this
$sth->FETCH( 'ParamValues' ), # WTF? - returns a reference to an array of
hashes
was most likely complaing that the
$sth->{ParamValues},
should return a ref but was just returning undef.
So he 'Kludged' the code to get the value directly with the FETCH which
works
I'm not sure what you're saying here John. Using $sth->FETCH('ParamValues')
is perfectly reasonable. It was required before 1.631 and optional with
1.631+ now that $h->{ParamValues} works.
sort of, but it does bleed memory every so slighly.
Are you sure? This is the first I've heard of such a leak.
Tim.
I've found no evidence of a memory leak with a simple test calling ParamValues
a lot with some parameters. However, I'm not using ORA_VARCHAR2_TABLE. The code
is:
else if (kl==11 && strEQ(key, "ParamValues")) {
HV *pvhv = newHV();
if (imp_sth->all_params_hv) {
SV *sv;
char *key;
I32 keylen;
hv_iterinit(imp_sth->all_params_hv);
while ( (sv = hv_iternextsv(imp_sth->all_params_hv, &key,
&keylen)) ) {
phs_t *phs = (phs_t*)(void*)SvPVX(sv); /*
placeholder struct */
(void)hv_store(pvhv, key, keylen,
newSVsv(phs->sv), 0);
}
}
retsv = newRV_noinc((SV*)pvhv);
cacheit = FALSE;
}
which looks sane to me right now. ORA_VARCHAR2_TABLE seems to do strange things
with parameters I don't quite get right now.
As I said previously to Hélder and John (some of the discussion was off
dbi-users list presumably because it contained log data), although I accept
taking the call to ParamValues out has on this occasion made the problem go
away I don't understand why. I think there is more to this than it so far looks
but without a way of reproducing it myself I won't be spending any more time on
it. If it is reproducible in a standalone script I will happily look again.
Martin
Re: Issues with DBI Oracle Input Array Binds (ORA_VARCHAR2_TABLE)
On Fri, Jan 31, 2014 at 09:11:28AM -0500, John Scoles wrote:
> A final note on this.
>
>Seems there was a very very long unknown bug in DBI which was only fix a
> few days ago wiht DB 1.6.31
If you mean Callbacks getting an inner handle, that wasn't a bug as such.
More like a design choice that proved non-optimal.
>
> [1]http://blogs.perl.org/mt/mt.fcgi?__mode=view&_type=entry&id=5570&blog_id=2165
That's http://blogs.perl.org/users/byterock/2014/01/callbacks-ate-my-brain.html
I presume.
>The end result of this bug was that when callbacks are used on the
>statement handle some attributes will not be there so you
>programmer who did this
>
>$sth->FETCH( 'ParamValues' ), # WTF? - returns a reference to an array of
> hashes
>
>was most likely complaing that the
>
>$sth->{ParamValues},
>
>should return a ref but was just returning undef.
>
>So he 'Kludged' the code to get the value directly with the FETCH which
> works
I'm not sure what you're saying here John. Using $sth->FETCH('ParamValues')
is perfectly reasonable. It was required before 1.631 and optional with
1.631+ now that $h->{ParamValues} works.
>sort of, but it does bleed memory every so slighly.
Are you sure? This is the first I've heard of such a leak.
Tim.
>The latest version of DBI with the
>
>$sth->{ParamValues},
>
>Should solve all you problems
>
>As a bonus I have another topic for me blog
>
>Cheers
>John
>
>
> ------------
>
>Date: Wed, 29 Jan 2014 14:21:28 +
>Subject: Re: Issues with DBI Oracle Input Array Binds (ORA_VARCHAR2_TABLE)
>From: hhferre...@gmail.com
>To: boh...@ntlworld.com
>CC: byter...@hotmail.com
>
>You are right Martin.. Shame on me :(
>At the time you suggested that we did not know about the callbacks, sorry
> for that, our fault :(�
>Now that we know the root of the problem I'm sure we will be able to
> implement a solution.�
>Thanks a lot for your time, tips and patience :)
>I would be more than pleased to offer you a ginginha, porto /portuguese
> wine accompanied by a special
>local cheese or a "portuguese egg tart" case you pass by here!
>Best Regards,
>H�lder Hugo Ferreira
>
>On Wed, Jan 29, 2014 at 12:04 PM, Martin J. Evans <[2]boh...@ntlworld.com>
> wrote:
>
> On 29/01/14 11:02, hhferreira wrote:
>
>Hey Guys!
>
>John, your tip about the callbacks revealed to be very accurate!!
>
> I seem to remember saying a long time back in this thread:
>
> "Have you got some sort of execute callback? I ask because of the
> following in the trace:
>
> � � {{ execute callback CODE(0xb832be8) being invoked
>
> and it is only present before the error."
>
> hmmm.
> �
>
>We managed to isolate the issue into this statement:
>
>� debug( "Executing SQL on OptiDb database:",
>� � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � �
> � �sql_executer � � � � =>
>sprintf( "%s line %s (%s)", (caller(0))[1,2],
> $sth->{private_keep_alive_seconds} ? 'active: keeping
>connection open' : 'maintenance' ),
>� � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � �
> � �statement � � � � � �=>
>$sth->{Statement},
>*params � � � � � � � => $sth->FETCH( 'ParamValues' ), # WTF? -
> returns a reference to an array of
>hashes*
>� � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � �
> � �connected_since � � �=>
>sprintf( "%s (%.3f seconds)",
>� � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � �
> � � � � � � � � � � � � � � �
>� � �strftime( "%H:%M:%S", localtime(
> $dbh->{private_connected_at_timestamp}[0] ) ),
>� � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � �
> � � � � � � � � � � � � � � �
>� � �tv_interval( $dbh->{private_connected_at_timestamp} ),
>� � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � �
> � � � � � � � � � � � � � � �
>� � �),
>� � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � �
> � �keep_alive_timestamp =>
>$dbh->{private_keep_alive_until_timestamp}
>� � � � � � � � � �
RE: Issues with DBI Oracle Input Array Binds (ORA_VARCHAR2_TABLE)
A final note on this.
Seems there was a very very long unknown bug in DBI which was only fix a few
days ago wiht DB 1.6.31
http://blogs.perl.org/mt/mt.fcgi?__mode=view&_type=entry&id=5570&blog_id=2165
The end result of this bug was that when callbacks are used on the statement
handle some attributes will not be there
so you programmer who did this
$sth->FETCH( 'ParamValues' ), # WTF? - returns a reference to an array of hashes
was most likely complaing that the
$sth->{ParamValues},
should return a ref but was just returning undef.
So he 'Kludged' the code to get the value directly with the FETCH which works
sort of, but it does bleed memory every so slighly.
The latest version of DBI with the
$sth->{ParamValues},
Should solve all you problems
As a bonus I have another topic for me blog
Cheers
John
Date: Wed, 29 Jan 2014 14:21:28 +
Subject: Re: Issues with DBI Oracle Input Array Binds (ORA_VARCHAR2_TABLE)
From: hhferre...@gmail.com
To: boh...@ntlworld.com
CC: byter...@hotmail.com
You are right Martin.. Shame on me :(
At the time you suggested that we did not know about the callbacks, sorry for
that, our fault :(á
Now that we know the root of the problem I'm sure we will be able to implement
a solution.á
Thanks a lot for your time, tips and patience :)
I would be more than pleased to offer you a ginginha, porto /portuguese wine
accompanied by a special local cheese or a "portuguese egg tart" case you pass
by here!
Best Regards,
HÚlder Hugo Ferreira
On Wed, Jan 29, 2014 at 12:04 PM, Martin J. Evans wrote:
On 29/01/14 11:02, hhferreira wrote:
Hey Guys!
John, your tip about the callbacks revealed to be very accurate!!
I seem to remember saying a long time back in this thread:
"Have you got some sort of execute callback? I ask because of the following in
the trace:
á á {{ execute callback CODE(0xb832be8) being invoked
and it is only present before the error."
hmmm.
á
We managed to isolate the issue into this statement:
á debug( "Executing SQL on OptiDb database:",
á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á
ásql_executer á á á á => sprintf( "%s line %s (%s)", (caller(0))[1,2],
$sth->{private_keep_alive_seconds} ? 'active: keeping connection open' :
'maintenance' ),
á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á
ástatement á á á á á á=> $sth->{Statement},
*params á á á á á á á => $sth->FETCH( 'ParamValues' ), # WTF? - returns a
reference to an array of hashes*
á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á
áconnected_since á á á=> sprintf( "%s (%.3f seconds)",
á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á
á á á á á á á á á á á á ástrftime( "%H:%M:%S", localtime(
$dbh->{private_connected_at_timestamp}[0] ) ),
á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á
á á á á á á á á á á á á átv_interval( $dbh->{private_connected_at_timestamp} ),
á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á
á á á á á á á á á á á á á),
á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á
ákeep_alive_timestamp => $dbh->{private_keep_alive_until_timestamp}
á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á
á á á á á á á á á á á? sprintf( "%s (%s seconds to live)",
á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á
á á á á á á á á á á á á á á á á ástrftime( "%H:%M:%S", localtime(
$dbh->{private_keep_alive_until_timestamp} ) ),
á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á
á á á á á á á á á á á á á á á á á( $dbh->{private_keep_alive_until_timestamp} -
time() ),
á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á
á á á á á á á á á á á á á á á á á)
á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á
á á á á á á á á á á á: '',
á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á á);
I guess the former developer added that suggestive comment for a reason :)
Now we don't really now how to resolve this issue here because this debug
function cannot simply be removed as it logs into a file very important data
required in case we need to track down business issues. I suppose the problem
here is that the ora_varchar2_table parameters are themselves arrays and when
they reach a certain size it crashes.
Any tip to replace this $sth->FETCH('ParamValues') statement into a workable
one? Maybe reduce the number of items in each of the inner arrays to 100 or so
could be a solution... We will try to investigate in this direction. If you
have some sort o
Re: Issues with DBI Oracle Input Array Binds (ORA_VARCHAR2_TABLE)
On 28/01/2014 10:56, hhferreira wrote: Hi John & Martin, Our application is single threaded and does not uses connection pool neither statement caching. Below I'm sending the code where new connections are created. Hi Hélder, I'm answering now as I don't want you to think I/we are ignoring you - certainly I am not. The most likely way for us/me to find a fix is if you provide us/me with some standalone code which demonstrates the problem. I've been asked this myself some times and it is also some times disheartening but the fact here is that your issue is complex and very difficult to diagnose remotely when we cannot see and execute the failing code. I wrote a tiny fraction of DBD::Oracle but help maintain it now and I don't even use the facility you are using and certainly never wrote it. I realise you may have "management" wanting a quick solution and I will definitely look at any code which reproduces the problem. Sadly, I don't right now see any other way to go. Martin -- Martin J. Evans Wetherby, UK
Re: Issues with DBI Oracle Input Array Binds (ORA_VARCHAR2_TABLE)
Hi Guys,
We have run a test with the latest DBI (1.631) and DBD (1.68) versions but
the same error persists. Our perl version is Active Perl v5.12.1.
However, we were able to generate a segmentation fault with a similar test
case passing as input 5000 test items in the arrays.
The core file info contains the following:
*#0 dbih_getcom (hrv=0x20202020) at DBI.xs:1133*
*#1 0xf6de5ae3 in XS_DBD__Oracle__st_execute (my_perl=0x9d3f008,
cv=0xaa21ba8) at ./Oracle.xsi:579*
*#2 0xf6e3347b in XS_DBI_dispatch (my_perl=0x9d3f008, cv=0xa93d160) at
DBI.xs:3746*
*#3 0x080d2e1f in Perl_pp_entersub ()*
*#4 0x080cb3b2 in Perl_runops_standard ()*
*#5 0x08074b02 in S_run_body ()*
*#6 0x0807499a in perl_run ()*
*#7 0x08060ded in main ()*
*(gdb) f 0*
*#0 dbih_getcom (hrv=0x20202020) at DBI.xs:1133*
*1133if ( SvROK(poolparty)*
*(gdb) l*
*1128{*
*1129MAGIC *mg;*
*1130SV *sv;*
*1131*
*1132/* short-cut common case */*
*1133if ( SvROK(poolparty)*
*1134&& (sv = SvRV(poolparty))*
*1135&& SvRMAGICAL(sv)*
*1136&& (mg = SvMAGIC(sv))*
*1137&& mg->mg_type == DBI_MAGIC*
Variable names are suggestive :)
We are a bit lost at the moment.. Any hint that can head us on the right
direction would be very appreciated.
Let me know in case you need further information to better analyze this
problem.
Thanks in advance.
Best Regards,
Hélder Hugo Ferreira
On Fri, Jan 24, 2014 at 3:41 PM, John Scoles wrote:
> Looking at the code changes I was mistaken that this has anyting to do
> with the native_execute aray. My Bad on that.
> That code had been around for a very long time well before 2005.
>
> As the value changes I would think there is some memory leak somplace that
> is corrupting the the 'all_params_hv' deep inside the 'c' struct?
>
> cheers
> ------------------
> Date: Fri, 24 Jan 2014 15:34:44 +
>
> Subject: Re: Issues with DBI Oracle Input Array Binds (ORA_VARCHAR2_TABLE)
> From: hhferre...@gmail.com
> To: boh...@ntlworld.com
> CC: martin.ev...@easysoft.com; byter...@hotmail.com; dbi-users@perl.org
>
> Theá3443804ávalue is the one we get most of the times but distinct values
> were already seen and were also big number which make no sense... Do you
> know if this function has been changed since DBD version 1.21?á
>
>
> All points out to be a memory leak I guess...
>
> Thanks!
> HÚlder
>
>
> On Fri, Jan 24, 2014 at 3:21 PM, Martin J. Evans wrote:
>
> On 24/01/14 14:59, hhferreira wrote:
>
> 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).
>
>
> Looks good to me. wc -l on the file shows 9389 and / 6 = ~ 1564
>
> I cannot as yet explain why dbdxst_bind_params thinks there are 3443804
> parameters.
>
> Even in hex 3443804 is 348c5c so it doesn't look like an overflow.
>
> The code reporting the issue is as follows:
>
> static int
> dbdxst_bind_params(SV *sth, imp_sth_t *imp_sth, I32 items, I32 ax)
> {
> á á /* Handle binding supplied values to placeholders. á á á á á*/
> á á /* items = one greater than the number of params á á á á á á*/
> á á /* ax = ax from calling sub, maybe adjusted to match items á*/
> á á dTHX;
> á á int i;
> á á SV *idx;
> á á if (items-1 != DBIc_NUM_PARAMS(imp_sth)
> á á á á && DBIc_NUM_PARAMS(imp_sth) != DBIc_NUM_PARAMS_AT_EXECUTE
> á á ) {
> á á á á char errmsg[99];
> á á á á /* clear any previous ParamValues before error is generated */
> á á á á SV **svp = hv_fetch((HV*)DBIc_MY_H(imp_
> sth),"ParamValues",11,FALSE);
> á á á á if (svp && SvROK(*svp) && SvTYPE(SvRV(*svp)) == SVt_PVHV) {
> á á á á á á HV *hv = (HV*)SvRV(*svp);
> á á á á á á hv_clear(hv);
> á á á á }
> á á á á sprintf(errmsg,"called with %d bind variables when %d are needed",
> á á á á á á á á (int)items-1, DBIc_NUM_PARAMS(imp_sth));
> á á á á DBIh_SET_ERR_CHAR(sth, (imp_xxh_t*)imp_sth, "-1", -1, errmsg,
> Nullch, Nullch);
> á á á á return 0;
> á á }
>
>
> and I don't particularly like that casting but I don't think it explains
> the problem.
>
> Martin
>
> 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
RE: Issues with DBI Oracle Input Array Binds (ORA_VARCHAR2_TABLE)
Looking at the code changes I was mistaken that this has anyting to do with the
native_execute aray. My Bad on that.
That code had been around for a very long time well before 2005.
As the value changes I would think there is some memory leak somplace that is
corrupting the the 'all_params_hv' deep inside the 'c' struct?
cheers
Date: Fri, 24 Jan 2014 15:34:44 +
Subject: Re: Issues with DBI Oracle Input Array Binds (ORA_VARCHAR2_TABLE)
From: hhferre...@gmail.com
To: boh...@ntlworld.com
CC: martin.ev...@easysoft.com; byter...@hotmail.com; dbi-users@perl.org
Theá3443804ávalue is the one we get most of the times but distinct values were
already seen and were also big number which make no sense... Do you know if
this function has been changed since DBD version 1.21?á
All points out to be a memory leak I guess...
Thanks!
HÚlder
On Fri, Jan 24, 2014 at 3:21 PM, Martin J. Evans wrote:
On 24/01/14 14:59, hhferreira wrote:
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).
Looks good to me. wc -l on the file shows 9389 and / 6 = ~ 1564
I cannot as yet explain why dbdxst_bind_params thinks there are 3443804
parameters.
Even in hex 3443804 is 348c5c so it doesn't look like an overflow.
The code reporting the issue is as follows:
static int
dbdxst_bind_params(SV *sth, imp_sth_t *imp_sth, I32 items, I32 ax)
{
á á /* Handle binding supplied values to placeholders. á á á á á*/
á á /* items = one greater than the number of params á á á á á á*/
á á /* ax = ax from calling sub, maybe adjusted to match items á*/
á á dTHX;
á á int i;
á á SV *idx;
á á if (items-1 != DBIc_NUM_PARAMS(imp_sth)
á á á á && DBIc_NUM_PARAMS(imp_sth) != DBIc_NUM_PARAMS_AT_EXECUTE
á á ) {
á á á á char errmsg[99];
á á á á /* clear any previous ParamValues before error is generated */
á á á á SV **svp = hv_fetch((HV*)DBIc_MY_H(imp_sth),"ParamValues",11,FALSE);
á á á á if (svp && SvROK(*svp) && SvTYPE(SvRV(*svp)) == SVt_PVHV) {
á á á á á á HV *hv = (HV*)SvRV(*svp);
á á á á á á hv_clear(hv);
á á á á }
á á á á sprintf(errmsg,"called with %d bind variables when %d are needed",
á á á á á á á á (int)items-1, DBIc_NUM_PARAMS(imp_sth));
á á á á DBIh_SET_ERR_CHAR(sth, (imp_xxh_t*)imp_sth, "-1", -1, errmsg, Nullch,
Nullch);
á á á á return 0;
á á }
and I don't particularly like that casting but I don't think it explains the
problem.
Martin
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 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 mailto:byter...@hotmail.com> <mailto:byter...@hotmail.c
RE: Issues with DBI Oracle Input Array Binds (ORA_VARCHAR2_TABLE)
Hmm
DBIc_NUM_PARAMS would be the same as (int)HvKEYS(imp_sth->all_params_hv)
so it is couning the keys on the 'all_params_hv' which is just a HV??
would have to put some debugging around it but all 'dbdxst_' would be in the
compiled Oracle.c code
not the DBD::Code??
Odd. Cross comple maybe or bug in the Oracle binarys??
> Date: Fri, 24 Jan 2014 15:21:47 +
> From: boh...@ntlworld.com
> To: hhferre...@gmail.com; martin.ev...@easysoft.com
> CC: byter...@hotmail.com; dbi-users@perl.org
> Subject: Re: Issues with DBI Oracle Input Array Binds (ORA_VARCHAR2_TABLE)
>
> On 24/01/14 14:59, hhferreira wrote:
> > 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).
>
> Looks good to me. wc -l on the file shows 9389 and / 6 = ~ 1564
>
> I cannot as yet explain why dbdxst_bind_params thinks there are 3443804
> parameters.
>
> Even in hex 3443804 is 348c5c so it doesn't look like an overflow.
>
> The code reporting the issue is as follows:
>
> static int
> dbdxst_bind_params(SV *sth, imp_sth_t *imp_sth, I32 items, I32 ax)
> {
> /* Handle binding supplied values to placeholders. */
> /* items = one greater than the number of params */
> /* ax = ax from calling sub, maybe adjusted to match items */
> dTHX;
> int i;
> SV *idx;
> if (items-1 != DBIc_NUM_PARAMS(imp_sth)
> && DBIc_NUM_PARAMS(imp_sth) != DBIc_NUM_PARAMS_AT_EXECUTE
> ) {
> char errmsg[99];
> /* clear any previous ParamValues before error is generated */
> SV **svp = hv_fetch((HV*)DBIc_MY_H(imp_sth),"ParamValues",11,FALSE);
> if (svp && SvROK(*svp) && SvTYPE(SvRV(*svp)) == SVt_PVHV) {
> HV *hv = (HV*)SvRV(*svp);
> hv_clear(hv);
> }
> sprintf(errmsg,"called with %d bind variables when %d are needed",
> (int)items-1, DBIc_NUM_PARAMS(imp_sth));
> DBIh_SET_ERR_CHAR(sth, (imp_xxh_t*)imp_sth, "-1", -1, errmsg, Nullch, Nullch);
> return 0;
> }
>
> and I don't particularly like that casting but I don't think it explains the
> problem.
>
> Martin
>
> > 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 > <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 Du
Re: Issues with DBI Oracle Input Array Binds (ORA_VARCHAR2_TABLE)
e $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> > 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 +
>> > 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> 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:
>> > >
>> >
>> >
>> > 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 2 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
>>
>>
>>
>>
>>
>
Re: Issues with DBI Oracle Input Array Binds (ORA_VARCHAR2_TABLE)
t;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:
> >
>
>
> 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 2 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
RE: Issues with DBI Oracle Input Array Binds (ORA_VARCHAR2_TABLE)
had a look at the data nothing poped out to me, such as a reserved word or
hidden contol character (\t and \x or UTF8)
Does the same number (ie 3443804 ) in the error
DB: DBD::Oracle::st execute failed: called with 3443804 bind variables when 7
are needed [for Statement..."/
come up each time or does it change?? I susspect this my just be the int
value of the callback and it would change each time you run?
cheers
John
Date: Fri, 24 Jan 2014 14:59:25 +
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
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 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 +
> From: boh...@ntlworld.com <mailto:boh...@ntlworld.com>
> To: hhferre...@gmail.com <mailto:hhferre...@gmail.com>;
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 =&
Re: Issues with DBI Oracle Input Array Binds (ORA_VARCHAR2_TABLE)
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 +
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 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 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
RE: Issues with DBI Oracle Input Array Binds (ORA_VARCHAR2_TABLE)
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.
Date: Fri, 24 Jan 2014 14:59:25 +
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
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 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 +
> From: boh...@ntlworld.com <mailto:boh...@ntlworld.com>
> To: hhferre...@gmail.com <mailto:hhferre...@gmail.com>;
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, /
> >
Re: Issues with DBI Oracle Input Array Binds (ORA_VARCHAR2_TABLE)
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 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 +
> From: boh...@ntlworld.com <mailto:boh...@ntlworld.com>
> To: hhferre...@gmail.com <mailto:hhferre...@gmail.com>; 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:
> >
>
>
> 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
&g
Re: Issues with DBI Oracle Input Array Binds (ORA_VARCHAR2_TABLE)
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
On Fri, Jan 24, 2014 at 12:09 PM, John Scoles 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 +
> > From: boh...@ntlworld.com
> > To: hhferre...@gmail.com; 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:
> > >
> >
> >
> > 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
> 2 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
>
RE: Issues with DBI Oracle Input Array Binds (ORA_VARCHAR2_TABLE)
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 +
> From: boh...@ntlworld.com
> To: hhferre...@gmail.com; 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:
> >
>
>
> 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 2
> > 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
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:
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 2 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
Re: Issues with DBI Oracle Input Array Binds (ORA_VARCHAR2_TABLE)
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
