Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

How to count number of rows in a cursor???

813537Nov 12 2010 — edited Nov 12 2010
I have a cursor and i want to check number of row fetched...But not through rowcount... bcoz it will give after fetching all the rows..

I want to get count as soon as i open the cursor..plz let me know

Comments

635471
Include COUNT(*) OVER () as a new column. Be prepared for it to go slower.
1 person found this helpful
6363
810534 wrote:
I have a cursor and i want to check number of row fetched...But not through rowcount... bcoz it will give after fetching all the rows..
Cursors do not store any rows they are a pointer to a compiled SQL statement that can be used to fetch the rows.
I want to get count as soon as i open the cursor..plz let me know
It will always be 0 until you fetch.
6363
David_Aldridge wrote:

Include COUNT(*) OVER () as a new column. Be prepared for it to go slower.
Agreed here is an example showing the addition of the count making it take over 6 minutes instead of one second to fetch the first row, or over 300 times slower.

3576433
hm
That won't work always.
An example for not working is the usage of distinct - clause, e.g.
with yourtable as
(
select 'A' a from dual union all
select 'A' a from dual
 )
select distinct a , count(*) over ()
from yourtable ;
Only one row will be retrieved, but "count(*) over()" shows 2 ! ! !

You could use "count(distinct a) over()" in my case. But that is not a general solution.

I even think, there is no general solution for the problem.

Edited by: hm on 12.11.2010 04:54
635471
hm wrote:
That won't work always.
An example for not working is the usage of distinct - clause, e.g.
with yourtable as
(
select 'A' a from dual union all
select 'A' a from dual
)
select distinct a , count(*) over ()
from yourtable ;
Only one row will be retrieved, but "count(*) over()" shows 2 ! ! !

You could use "count(distinct a) over()" in my case. But that is not a general solution.

I even think, there is no general solution for the problem.

Edited by: hm on 12.11.2010 04:54
hmmm ... you'd have to wrap the query in an inline view and apply the count(*) over() in the main query I guess.
hm
Ok, that might work:
with yourtable as
(
select 'A' a from dual union all
select 'A' a from dual
 )
-- Query:
select x.*, 
count(*) over()
from
(
/* put your query here: */
select distinct a
from yourtable 
/* end of your query */
) x;
BluShadow
Cursors do not know how many rows there are when the cursor is opened because no rows have been fetched at that point.

Sounds like there's a lack of understanding of what a cursor is...

888365
(this is about ref cursors but it applies to other types of cursors too)
813673
declare
cursor c is select * from emp;
cursor_count c%rowtype;
totalrows number;
begin
open c;
loop
fetch c into cursor_count;
dbms_output.put_line('names' ||cursor_count.ename);
exit when c%notfound;
end loop;
totalrows:=c%rowcount;
dbms_output.put_line('total rows' || totalrows);
end;

Edited by: 810670 on Nov 12, 2010 8:42 AM
813673
declare
cursor c is select * from emp;
cursor_count c%rowtype;
totalrows number;
begin
open c;
loop
fetch c into cursor_count;
dbms_output.put_line('names' ||cursor_count.ename);
exit when c%notfound;
end loop;
totalrows:=c%rowcount;
dbms_output.put_line('total rows' || totalrows);
end;
Solomon Yakobson
David_Aldridge wrote:

hmmm ... you'd have to wrap the query in an inline view and apply the count(*) over() in the main query I guess.
Still will not cover all cases:

Session 1:
update emp set deptno = deptno where deptno = 20
/

5 rows updated.

SQL>
Session 2:
select  ename,
        count(*) over()
  from  emp
  for update
  skip locked
/

ENAME      COUNT(*)OVER()
---------- --------------
ALLEN                  14
WARD                   14
MARTIN                 14
BLAKE                  14
CLARK                  14
KING                   14
TURNER                 14
JAMES                  14
MILLER                 14

9 rows selected.
Now try to wrap the above query in an inline view :).

SY.
1 - 10
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Dec 10 2010
Added on Nov 12 2010
10 comments
98,268 views