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

Home -> Community -> Usenet -> c.d.o.server -> Re: Newbie question: SQL*Plus command to see number of query result rows

Re: Newbie question: SQL*Plus command to see number of query result rows

From: Martin Mølskov Hansen <mmh_at_kab-bolig.dk>
Date: Sat, 13 Mar 2004 18:20:16 +0100
Message-ID: <4053416e$0$1660$edfadb0f@dread14.news.tele.dk>


You are asking if it´s possible to remove the basic function of Sql*plus.

You can remove the header "STUDENT_ID" using this command: set heading off; And you can remove all feedbacks ie. "3 rows selected" by using this command: set feedback off;

But you can not remove the results, since they are the once you are requesting in your select statement.

Why not use:
 select count(1) from ( your joined statement );

Or try using a cursor.

You can cut script below and save it to <name>.sql and execute it in Sql*plus.
Remember to replace <Your joined statement.> with, well guess...¨

Declare

Cursor A1 is
 <Your joined statement.>

r1 A1%rowtype;

cnt number;

Begin

 cnt := 0;

 Open A1;
  Loop
   Fetch A1 into r1;
   Exit when A1%notfound;

    cnt := cnt + 1;

   End loop;

 Close A1;

 dbms_output.put_line('Result: '||cnt);

End;
/
---- END CUT ---- Regards
Martin M. Hansen

"Lara" <larroyo114_at_yahoo.com> skrev i en meddelelse news:73447077.0403130816.3bf4ae67_at_posting.google.com...
> Hi, I have what I think is a simple SQL*Plus question, but I can't
> find the answer in any of my books.
>
> I'm trying to turn off SQL*Plus displaying the result of each row in a
> select statement, and just want to see the number of rows the select
> statement would generate. I'm trying to see if my select statement is
> correctly generating the correct number of rows. I know it should
> generate 154,280 rows, but it takes too long to sit here and wait to
> see the results if every row of the query result displays.
>
> In other words (in a shortened example), I currently have:
>
> SQL> Select student_id from student;
>
> STUDENT_ID
> ----------
> 391
> 392
> 393
> 3 rows selected.
>
>
> Is there any command I can use to get:
> SQL> Select student_id from student;
>
> 3 rows selected.
>
>
> p.s. I don't think I can use COUNT because there are multiple joins
> and subqueries throughout the select.
>
> Thanks!
Received on Sat Mar 13 2004 - 11:20:16 CST

Original text of this message

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