Skip Menu |
 

Preferred bug tracker

Please visit the preferred bug tracker to report your issue.

This queue is for tickets about the DBD-Oracle CPAN distribution.

Report information
The Basics
Id: 76410
Status: resolved
Priority: 0/
Queue: DBD-Oracle

People
Owner: Nobody in particular
Requestors: DAMI [...] cpan.org
Cc:
AdminCc:

Bug Information
Severity: Important
Broken in: 1.42
Fixed in: 1.45_00



Subject: misbehaviour of scrollable cursors
Download (untitled) / with headers
text/plain 950b
Two problems with scrollable cursors (with DBD::Oracle v1.42, OCI version 11.2.0.2) Given the following : my $sql = "SELECT table_name FROM all_tables ORDER BY table_name "; my $sth = $dbh->prepare($sql, {ora_exe_mode => OCI_STMT_SCROLLABLE_READONLY}); $sth->execute; Problem 1 : $sth->ora_fetch_scroll(OCI_FETCH_ABSOLUTE, $some_offset); my $r1 = $sth->ora_fetch_scroll(OCI_FETCH_NEXT, 0); my $r2 = $sth->ora_fetch_scroll(OCI_FETCH_NEXT, 0); $r2 is a different reference from $r1, but the content of @$r1 is overwritten with the content of @$r2 ! Problem 2 : $sth->ora_fetch_scroll(OCI_FETCH_ABSOLUTE, $some_offset); my $r1 = $sth->fetch; my $r2 = $sth->fetch; ... Every successive call to the ->fetch() method always returns the same record. And of course, if you try $sth->fetchall_arrayref(), you get into an infinite loop. However, if you do one single OCI_FETCH_NEXT after the ABSOLUTE, then the behaviour of ->fetch() seems to be OK.
From: byterock [...] hotmail.com
Download (untitled) / with headers
text/plain 1.2k
On Sun Apr 08 12:19:50 2012, DAMI wrote: Show quoted text
> Two problems with scrollable cursors (with DBD::Oracle v1.42, OCI > version 11.2.0.2) > > Given the following : > > my $sql = "SELECT table_name FROM all_tables ORDER BY table_name "; > my $sth = $dbh->prepare($sql, {ora_exe_mode => > OCI_STMT_SCROLLABLE_READONLY}); > $sth->execute; > > > Problem 1 : > $sth->ora_fetch_scroll(OCI_FETCH_ABSOLUTE, $some_offset); > my $r1 = $sth->ora_fetch_scroll(OCI_FETCH_NEXT, 0); > my $r2 = $sth->ora_fetch_scroll(OCI_FETCH_NEXT, 0); > $r2 is a different reference from $r1, but the content of @$r1 > is overwritten with the content of @$r2 ! >
Actully the are exatly the same A referace to '$sth->ora_fetch_scroll(OCI_FETCH_NEXT, 0);' which would be the next fetch. I think you are just getting into a circular referace here Just my first though. Show quoted text
> Problem 2 : > $sth->ora_fetch_scroll(OCI_FETCH_ABSOLUTE, $some_offset); > my $r1 = $sth->fetch; > my $r2 = $sth->fetch; > ... > Every successive call to the ->fetch() method always returns the same > record. And of course, if you try $sth->fetchall_arrayref(), you get > into an infinite loop. However, if you do one single OCI_FETCH_NEXT > after the ABSOLUTE, then the behaviour of ->fetch() seems to be OK. >
Subject: Re: [rt.cpan.org #76410] misbehaviour of scrollable cursors
Date: Mon, 09 Apr 2012 12:02:37 -0400
To: bug-DBD-Oracle [...] rt.cpan.org
From: Yanick Champoux <champoux [...] pythian.com>
Download (untitled) / with headers
text/plain 1.5k
Hi Laurent, Huge caveat: I'm not overly familiar with ora_fetch_scroll, so take the following with a grain of salt. :-) On 12-04-08 12:19 PM, Laurent Dami via RT wrote: Show quoted text
> > Problem 1 : > $sth->ora_fetch_scroll(OCI_FETCH_ABSOLUTE, $some_offset); > my $r1 = $sth->ora_fetch_scroll(OCI_FETCH_NEXT, 0); > my $r2 = $sth->ora_fetch_scroll(OCI_FETCH_NEXT, 0); > $r2 is a different reference from $r1, but the content of @$r1 > is overwritten with the content of @$r2 !
That sounds... vicious. It'll double-check the docs to make sure it's not meant to be that way. From your report, it sounds like all 'ora_fetch_scroll' calls use the same underlying array. Show quoted text
> > Problem 2 : > $sth->ora_fetch_scroll(OCI_FETCH_ABSOLUTE, $some_offset); > my $r1 = $sth->fetch; > my $r2 = $sth->fetch; > ... > Every successive call to the ->fetch() method always returns the same > record.
Are you hitting the last row? The doc says of OCI_FETCH_ABSOLUTE: "[..] and a fetch offset value that is greater than the number of records in the record set, does not change current_positon attribute, it is equivalent to a OCI_FETCH_CURRENT." Which might explain the symptoms you're seeing. Joy, `/anick Show quoted text
> And of course, if you try $sth->fetchall_arrayref(), you get > into an infinite loop. However, if you do one single OCI_FETCH_NEXT > after the ABSOLUTE, then the behaviour of ->fetch() seems to be OK. > >
-- Yanick Champoux, Senior Perl Developer The Pythian Group - love your data http://www.pythian.com -- -- Pythian proud winner of Oracle North America Titan Award for Exadata Solution...watch the video on pythian.com
Subject: Re: [rt.cpan.org #76410] misbehaviour of scrollable cursors
Date: Mon, 09 Apr 2012 14:49:25 -0400
To: bug-DBD-Oracle [...] rt.cpan.org
From: Yanick Champoux <champoux [...] pythian.com>
Download (untitled) / with headers
text/plain 1.1k
On 12-04-08 12:19 PM, Laurent Dami via RT wrote: Show quoted text
> Problem 1 : > $sth->ora_fetch_scroll(OCI_FETCH_ABSOLUTE, $some_offset); > my $r1 = $sth->ora_fetch_scroll(OCI_FETCH_NEXT, 0); > my $r2 = $sth->ora_fetch_scroll(OCI_FETCH_NEXT, 0); > $r2 is a different reference from $r1, but the content of @$r1 > is overwritten with the content of @$r2 !
The undesired behavior is captured in a test at https://github.com/yanick/DBD-Oracle/tree/rt76410-scroll-misbehave The jury is still out as to if that's the way it should be. In the meantime, to ensure that your next call to the function doesn't mess with your results, you can do: my $r1 = \@{$sth->ora_fetch_scroll(OCI_FETCH_NEXT, 0)}; my $r2 = \@{$sth->ora_fetch_scroll(OCI_FETCH_NEXT, 0)}; It's slightly silly, granted, but until/if we correct the behavior, it's duct-tape that should get you going. :-) Cheers, `/anick -- Yanick Champoux, Senior Perl Developer The Pythian Group - love your data http://www.pythian.com -- -- Discover the latest MySQL tips and tricks from Pythian’s top talent at this year’s MySQL Conference April 10-12. Details at pythian.com/news
Subject: Re: [rt.cpan.org #76410] misbehaviour of scrollable cursors
Date: Mon, 16 Apr 2012 03:15:35 +0200
To: bug-DBD-Oracle [...] rt.cpan.org
From: laurent dami <laurent.dami [...] free.fr>
Download (untitled) / with headers
text/plain 1.1k
Le 09.04.2012 18:02, Pythian Remote DBA via RT a écrit : Show quoted text
>> Problem 2 : >> $sth->ora_fetch_scroll(OCI_FETCH_ABSOLUTE, $some_offset); >> my $r1 = $sth->fetch; >> my $r2 = $sth->fetch; >> ... >> Every successive call to the ->fetch() method always returns the same >> record.
> Are you hitting the last row? The doc says of OCI_FETCH_ABSOLUTE: > "[..] and a fetch offset value that is greater than the number of > records in the record set, does not change current_positon attribute, it > is equivalent to a OCI_FETCH_CURRENT." Which might explain the symptoms > you're seeing. > > >
No I'm not at the last row. The value of $some_offset is right in the middle of the result set. Since the doc says "When the statement is executed you will then be able to use 'ora_fetch_scroll' method to get a row or you can still use any of the other fetch methods", one would expect to be able to freely mix calls to ->fetch() and calls to ->ora_fetch_scroll(), but this is not the observed behaviour. Something like this snippet works correctly : $sth->ora_fetch_scroll(OCI_FETCH_ABSOLUTE, $some_offset- 1); $sth->ora_fetch_scroll(OCI_FETCH_NEXT, 0); my $r1 = $sth->fetch; my $r2 = $sth->fetch; ...
Download (untitled) / with headers
text/plain 909b
On Sun Apr 08 12:19:50 2012, DAMI wrote: Show quoted text
> Two problems with scrollable cursors (with DBD::Oracle v1.42, OCI > version 11.2.0.2) > > Given the following : > > my $sql = "SELECT table_name FROM all_tables ORDER BY table_name "; > my $sth = $dbh->prepare($sql, {ora_exe_mode => > OCI_STMT_SCROLLABLE_READONLY}); > $sth->execute; > > > Problem 1 : > $sth->ora_fetch_scroll(OCI_FETCH_ABSOLUTE, $some_offset); > my $r1 = $sth->ora_fetch_scroll(OCI_FETCH_NEXT, 0); > my $r2 = $sth->ora_fetch_scroll(OCI_FETCH_NEXT, 0); > $r2 is a different reference from $r1, but the content of @$r1 > is overwritten with the content of @$r2 !
That is not quite as easy to fix as the other part of this rt. Show quoted text
> Problem 2 : > $sth->ora_fetch_scroll(OCI_FETCH_ABSOLUTE, $some_offset); > my $r1 = $sth->fetch; > my $r2 = $sth->fetch; > ...
This should be fixed in subversion trunk now. Martin -- Martin J. Evans Wetherby, UK
Download (untitled) / with headers
text/plain 1.9k
On Sun Apr 08 12:19:50 2012, DAMI wrote: Show quoted text
> Two problems with scrollable cursors (with DBD::Oracle v1.42, OCI > version 11.2.0.2) > > Given the following : > > my $sql = "SELECT table_name FROM all_tables ORDER BY table_name "; > my $sth = $dbh->prepare($sql, {ora_exe_mode => > OCI_STMT_SCROLLABLE_READONLY}); > $sth->execute; > > > Problem 1 : > $sth->ora_fetch_scroll(OCI_FETCH_ABSOLUTE, $some_offset); > my $r1 = $sth->ora_fetch_scroll(OCI_FETCH_NEXT, 0); > my $r2 = $sth->ora_fetch_scroll(OCI_FETCH_NEXT, 0); > $r2 is a different reference from $r1, but the content of @$r1 > is overwritten with the content of @$r2 !
Strange, I don't get that, I get the same ref in each case. # rt 76410 - fetch next twice returns different references but the # contents of the first are overwritten by the contents of the second $value = $sth->ora_fetch_scroll(OCI_FETCH_ABSOLUTE, 1); is($value->[0], 1, "... we should get the 1st row rt76410_1"); my $v1 = $sth->ora_fetch_scroll(OCI_FETCH_NEXT, 0); is($v1->[0], 2, "... we should get the 2nd row rt76410_1"); my $v2 = $sth->ora_fetch_scroll(OCI_FETCH_NEXT, 0); is($v2->[0], 3, "... we should get the 3rd row rt76410_1"); ok($v1 != $v2, "... two row references are different rt76410_1") or diag("$v1, $v2"); isnt($v1->[0], $v2->[0], "... 1st fetched row should not change rt76410_1"); ok 37 - ... we should get the 1st row rt76410_1 ok 38 - ... we should get the 2nd row rt76410_1 ok 39 - ... we should get the 3rd row rt76410_1 not ok 40 - ... two row references are different rt76410_1 # Failed test '... two row references are different rt76410_1' # at t/51scroll.t line 139. # ARRAY(0x9edd870), ARRAY(0x9edd870) not ok 41 - ... 1st fetched row should not change rt76410_1 # Failed test '... 1st fetched row should not change rt76410_1' # at t/51scroll.t line 140. # got: '3' # expected: anything else I don't think the other fixes affect this. Martin -- Martin J. Evans Wetherby, UK
Download (untitled) / with headers
text/plain 2.5k
On Sun Apr 22 04:41:44 2012, MJEVANS wrote: Show quoted text
> On Sun Apr 08 12:19:50 2012, DAMI wrote:
> > Two problems with scrollable cursors (with DBD::Oracle v1.42, OCI > > version 11.2.0.2) > > > > Given the following : > > > > my $sql = "SELECT table_name FROM all_tables ORDER BY table_name "; > > my $sth = $dbh->prepare($sql, {ora_exe_mode => > > OCI_STMT_SCROLLABLE_READONLY}); > > $sth->execute; > > > > > > Problem 1 : > > $sth->ora_fetch_scroll(OCI_FETCH_ABSOLUTE, $some_offset); > > my $r1 = $sth->ora_fetch_scroll(OCI_FETCH_NEXT, 0); > > my $r2 = $sth->ora_fetch_scroll(OCI_FETCH_NEXT, 0); > > $r2 is a different reference from $r1, but the content of @$r1 > > is overwritten with the content of @$r2 !
> > Strange, I don't get that, I get the same ref in each case. > > # rt 76410 - fetch next twice returns different references but the > # contents of the first are overwritten by the contents of the second > $value = $sth->ora_fetch_scroll(OCI_FETCH_ABSOLUTE, 1); > is($value->[0], 1, "... we should get the 1st row rt76410_1"); > my $v1 = $sth->ora_fetch_scroll(OCI_FETCH_NEXT, 0); > is($v1->[0], 2, "... we should get the 2nd row rt76410_1"); > my $v2 = $sth->ora_fetch_scroll(OCI_FETCH_NEXT, 0); > is($v2->[0], 3, "... we should get the 3rd row rt76410_1"); > ok($v1 != $v2, "... two row references are different rt76410_1") or > diag("$v1, $v2"); > isnt($v1->[0], $v2->[0], "... 1st fetched row should not change
rt76410_1"); Show quoted text
> > ok 37 - ... we should get the 1st row rt76410_1 > ok 38 - ... we should get the 2nd row rt76410_1 > ok 39 - ... we should get the 3rd row rt76410_1 > not ok 40 - ... two row references are different rt76410_1 > > # Failed test '... two row references are different rt76410_1' > # at t/51scroll.t line 139. > # ARRAY(0x9edd870), ARRAY(0x9edd870) > not ok 41 - ... 1st fetched row should not change rt76410_1 > > # Failed test '... 1st fetched row should not change rt76410_1' > # at t/51scroll.t line 140. > # got: '3' > # expected: anything else > > I don't think the other fixes affect this. > > Martin
From the DBD::Oracle pod http://search.cpan.org/~pythian/DBD-Oracle-1.42/lib/DBD/Oracle.pm#Scrollable_Cursor_Methods ora_fetch_scroll "Works the same as fetchrow_arrayref" From the DBI pod http://search.cpan.org/~timb/DBI-1.618/DBI.pm#fetchrow_arrayref Note that the same array reference is returned for each fetch, so don't store the reference and then use it after a later fetch. So the first part of this rt is working as per documented. Martin -- Martin J. Evans Wetherby, UK


This service is sponsored and maintained by Best Practical Solutions and runs on Perl.org infrastructure.

Please report any issues with rt.cpan.org to rt-cpan-admin@bestpractical.com.