On Mon Mar 25 14:41:22 2013, MJEVANS wrote:
Show quoted text> On Sat Mar 23 19:40:23 2013, DAMI wrote:
> > Yet another problem with scrollable cursors, similar to #76695 and
> > #76410 : if we use ora_fetch_scroll() to go to some specific row,
> > and then loop over the remaing rows with one of the fetch_*
> > methods, then we get stuck on the last row (fetch() never returns
> > undef).
> >
> >
> > Ex:
> > 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;
> > $sth->ora_fetch_scroll(OCI_FETCH_LAST, 0);
> > $sth->ora_fetch_scroll(OCI_FETCH_RELATIVE, -2);
> > for (1 .. 5) { # purposedly trying beyond the last row
> > my $r = $sth->fetch; # should get undef at some point .. but no
> > say @$r;
> > }
> >
> > Yields:
> > WRI$_ADV_ASA_RECO_DATA
> > WRR$_REPLAY_CALL_FILTER
> > WRR$_REPLAY_CALL_FILTER
> > WRR$_REPLAY_CALL_FILTER
> > WRR$_REPLAY_CALL_FILTER
> > ...
>
> Thank you for this report.
>
> I'll try and find some tuits to look in to it soon but my focus is
> elsewhere right now and I did not write scrollable cursor support
> (John Scoles did).
>
> You could perhaps help yourself by enabling ora_verbose=6 in the
> connect method call and looking at the trace output. My recollection
> is that the code is not that complex and you'll find most of it in
> oci8.c.
>
> Martin
ok, I saved someone (perhaps me) some time on this one:
use DBI;
use strict;
use warnings;
use DBD::Oracle qw(:ora_fetch_orient :ora_exe_modes);
my $h = DBI->connect("dbi:Oracle:host=xxx;sid=xxx","xxx","xxx",
{RaiseError => 1, ora_verbose => 6});
eval {
$h->do(q/drop table mje/);
};
$h->do(q/create table mje (a int)/);
foreach (1..5) {
$h->do(qq/insert into mje values($_)/);
}
my $sql = "SELECT * from mje";
my $sth = $h->prepare($sql, {ora_exe_mode => OCI_STMT_SCROLLABLE_READONLY});
$sth->execute;
$sth->ora_fetch_scroll(OCI_FETCH_LAST, 0);
$sth->ora_fetch_scroll(OCI_FETCH_RELATIVE, -2);
for (1 .. 5) { # purposedly trying beyond the last row
my $r = $sth->fetch; # should get undef at some point .. but no
print @$r, "\n";
}
produces trace output:
4
dbd_st_fetch 1 fields...
Scrolling Fetch, position before fetch=4, Orientation = OCI_FETCH_NEXT , Fetchoffset =1
OCIStmtFetch(8473a74,8467e34,1,2,1)=SUCCESS <--- NOTICE SUCCESS
OCIAttrGet(8473a74,OCI_HTYPE_STMT,848aaf0,0,OCI_ATTR_CURRENT_POSITION,8467e34)=SUCCESS
Scrolling Fetch, postion after fetch=5
dbd_st_fetched 1 fields with status of 0(SUCCESS)
field #1 with rc=0(OK)
821b6c0 (field=0): '5'
5
dbd_st_fetch 1 fields...
Scrolling Fetch, position before fetch=5, Orientation = OCI_FETCH_NEXT , Fetchoffset =1
OCIStmtFetch(8473a74,8467e34,1,2,1)=NO_DATA <--- NOTICE NO_DATA
Seems like the scrollable cursor code is not paying attention to NO_DATA.
Martin
--
Martin J. Evans
Wetherby, UK
