Re: Getting Record Count from SELECTs

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Tue, 11 Oct 2005 18:57:16 +0200
Message-ID: <434beeea$0$5305$626a14ce@news.free.fr>

"GeoPappas" <PappasG_at_gmail.com> a écrit dans le message de news: 1129045541.886804.208700_at_g49g2000cwa.googlegroups.com...
| Is there any way to get the record count from a SELECT statement
| without having to run another query?
|
| For example, say you have a query as follows:
|
| SELECT * FROM temp WHERE create_date < TO_DATE( '01/01/2005',
| 'MM/DD/RRRR' );
|
| Is there any way to get the record count without having to run anothe
| SELECT, such as:
|
| SELECT COUNT(*) FROM temp WHERE create_date < TO_DATE( '01/01/2005',
| 'MM/DD/RRRR' );
|

Here's an excellent exemple from Tom Kyte:

ops$tkyte_at_ORA920.US.ORACLE.COM> declare

  2      type array is table of number index by binary_integer;
  3      l_data array;
  4      cursor c is select empno from emp;
  5  begin
  6      open c;
  7      loop
  8          fetch c bulk collect into l_data limit 5;
  9          /* process data here */
 10
 11          dbms_output.put_line( 'Looping, c%rowcount = ' || c%rowcount );
 12          exit when c%notfound;
 13      end loop;
 14      dbms_output.put_line( 'out of loop, c%rowcount = ' || c%rowcount );
 15      close c;

 16 end;
 17 /

Looping, c%rowcount = 5
Looping, c%rowcount = 10
Looping, c%rowcount = 14

out of loop, c%rowcount = 14

PL/SQL procedure successfully completed.

Regards
Michel Cadot Received on Tue Oct 11 2005 - 11:57:16 CDT