Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: SQL: put numbers of rows in a variable
A copy of this was sent to d.segers_at_kpn.com (Dick Segers)
(if that email address didn't require changing)
On Tue, 02 Nov 1999 13:29:57 GMT, you wrote:
>Hi there,
>
>From some tables in my database I want to know the number of rows.
>So I use:
>
>SQL> Select count(*) from tablename;
>
>But now I want to fetch the result in a variable ( so I can make some
>readable output )
>
>========
>
>When that is done I want to make an overview of all user_tables with
>there number of rows.
>
>
>
>Thanks in advance,
>
>Dick Segers.
here are 2 ways:
tkyte_at_8.0> column cnt new_value X tkyte_at_8.0> column cnt noprint tkyte_at_8.0> select count(*) cnt from dual; tkyte_at_8.0> prompt And the Answer was &X row(s) And the Answer was 1 row(s)
tkyte_at_8.0> set serveroutput on
tkyte_at_8.0> begin
2 for x in ( select count(*) cnt from dual ) loop 3 dbms_output.put_line( 'There are ' || x.cnt || ' row(s)' ); 4 end loop;
--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
Current article is "Part I of V, Autonomous Transactions" updated June 21'st
Thomas Kyte tkyte_at_us.oracle.com Oracle Service Industries Reston, VA USA
Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Tue Nov 02 1999 - 08:20:26 CST