http://qs321.pair.com?node_id=674739


in reply to Re: Counting number of rows while working with Oracle
in thread Counting number of rows while working with Oracle

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.

Replies are listed 'Best First'.
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);

    or

    my $row = $sth->fetch_arrayref(); $sth->finish();

    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();

    defined($value) will tell him if a value was returned (again, assuming the value can't be NULL).

      Please tell me what happens if the query return more than one row. would $sth->fetchall_arrayref(); work?
        Please read the manual and tell us which part you don't understand.
        --
        Andreas
      Thanks for the replies I have tried the following code
      my $rows = $sthBoo->fetchall_arrayref(); my $num_rows = @$rows;
      it worked I could see the number of rows but now if I use
      while (my $row = $sth->fetchrow_hashref) {
      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 "
      Is there any problem withthe code? Thanks
        That's why I followed those two lines with foreach my $row (@$rows) instead of while (my $row = $sth->fetchrow_hashref).
        You did not post a complete, working code sample, so we can't say.

        Inspect your $sth vs. $sthBoo though.

        --
        Andreas