Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: SQL: put numbers of rows in a variable

Re: SQL: put numbers of rows in a variable

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Tue, 02 Nov 1999 09:20:26 -0500
Message-ID: <DvMeOBILk0PfkPQzzff2BR8XytfH@4ax.com>


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;

  5 end;
  6 /
There are 1 row(s)

--
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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US