in reply to 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.

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]

      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]
        Please tell me what happens if the query return more than one row. would $sth->fetchall_arrayref(); work?
[reply]
[d/l]
        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]

In Section Seekers of Perl Wisdom