Re: Counting number of rows while working with Oracle

As discussed in the CB, databases tend to not tell the client how many rows they will return. The only way for the client to know how many rows will be returned is to count them as they're being read in.

The simplest solution would to read all the rows into memory before starting to process them. For example, replace

while (my $row = $sth->fetch_arrayref()) {
   ...
}
[download]

with

my $rows = $sth->fetchall_arrayref();
my $num_rows = @$rows;
foreach my $row (@$rows) {
   ...
}
[download]

If you need a DBI interface, then you can replace the code with

my $rows = $sth->fetchall_arrayref();
my $num_rows = @$rows;

my $sponge = DBI->connect('dbi:Sponge:');
my $sponge_sth = $sponge->prepare(
   $sth->{Statement},
   {
      rows        => $rows,
      NAME        => $sth->{NAME},
      behave_like => $sth,
   }
);

while (my $row = $sponge_sth->fetch_arrayref()) {
   ...
}
[download]

It's too bad that DBD::Sponge doesn't override rows.

Comment on Re: Counting number of rows while working with Oracle Select or Download Code

Replies are listed 'Best First'.
Re^2: Counting number of rows while working with Oracle by Thilosophy (Curate) on Mar 18, 2008 at 08:12 UTC
The only way for the client to know how many rows will be returned is to count them as they're being read in. The simplest solution would to read all the rows into memory before starting to process them. Especially since you say your query will only return at most a single row, using `fetchall_arrayref` or the even more convenient `selectall_arrayref` should be no problem.	[reply] [d/l] [select]
Re^3: Counting number of rows while working with Oracle by ikegami (Patriarch) on Mar 18, 2008 at 08:16 UTC
Oh, I missed that bit about returning at most one row! He can simply use `my $row = $dbh->selectrow_arrayref($stmt_or_sth);` [download] or `my $row = $sth->fetch_arrayref(); $sth->finish();` [download] `defined($row)` will tell him if a row was returned. If the row only contains one field and it can't be NULL, then he could even use `my ($value) = $sth->fetch_array(); $sth->finish();` [download] `defined($value)` will tell him if a value was returned (again, assuming the value can't be NULL).	[reply] [d/l] [select]
Re^4: Counting number of rows while working with Oracle by denzil_cactus (Sexton) on Mar 18, 2008 at 12:12 UTC
Please tell me what happens if the query return more than one row. would `$sth->fetchall_arrayref();` work?	[reply] [d/l]
Re^5: Counting number of rows while working with Oracle by andreas1234567 (Vicar) on Mar 18, 2008 at 12:24 UTC
Re^6: Counting number of rows while working with Oracle by denzil_cactus (Sexton) on Mar 20, 2008 at 07:12 UTC
Re^4: Counting number of rows while working with Oracle by denzil_cactus (Sexton) on Mar 18, 2008 at 11:02 UTC
Thanks for the replies I have tried the following code `my $rows = $sthBoo->fetchall_arrayref(); my $num_rows = @$rows;` [download] it worked I could see the number of rows but now if I use `while (my $row = $sth->fetchrow_hashref) {` [download] after the above code its giving the warning `DBD::Oracle::st fetchrow_hashref failed: no statement executing (perha +ps you need to call execute first) [for Statement "` [download] Is there any problem withthe code? Thanks	[reply] [d/l] [select]
Re^5: Counting number of rows while working with Oracle by ikegami (Patriarch) on Mar 18, 2008 at 12:52 UTC
Re^5: Counting number of rows while working with Oracle by andreas1234567 (Vicar) on Mar 18, 2008 at 11:42 UTC
Re^6: Counting number of rows while working with Oracle by denzil_cactus (Sexton) on Mar 20, 2008 at 08:44 UTC


Think about Loose Coupling
	PerlMonks