Home » SQL & PL/SQL » SQL & PL/SQL » How do I get the total row count within the cursor
How do I get the total row count within the cursor [message #242971] Tue, 05 June 2007 11:22 Go to next message
haiza
Messages: 22
Registered: June 2007
Junior Member
I'm using the For Cursor and I want to know how many records have been retrieved into the cursor. I don't want to use the count(*) within the SQL query. can someone help?
Re: How do I get the total row count within the cursor [message #242973 is a reply to message #242971] Tue, 05 June 2007 11:26 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
There is no way to know how many rows will be fetched from a cursor until you fetched them all.

Regards
Michel

[Updated on: Tue, 05 June 2007 11:27]

Report message to a moderator

Re: How do I get the total row count within the cursor [message #243037 is a reply to message #242971] Tue, 05 June 2007 15:12 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
If you want to keep track of the number of items already fetched, simply use a counter in your cursor-for loop.
Re: How do I get the total row count within the cursor [message #243040 is a reply to message #243037] Tue, 05 June 2007 15:24 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
In this case %ROWCOUNT cursor attribute is there.

Regards
Michel
Re: How do I get the total row count within the cursor [message #243041 is a reply to message #243040] Tue, 05 June 2007 15:33 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
ah, sure. I can't get the idea out of my head that that returns 1 in a cursor-for loop. Somebody, long ago planted that stupid thought, and now I'm stuck with it, I'm afraid Sad

Anyway, %rowcount seems unusable for cursors that are not explicitly declared. (Or I must be overlooking an obvious way yet again)
SQL> declare
  2    cursor c
  3    is
  4      select * from (select rownum rn from dual connect by level < 11);
  5  begin
  6    for r in c
  7    loop
  8      dbms_output.put_line('Row '||r.rn||' is '||c%rowcount);
  9    end loop;
 10  end;
 11  /
Row 1 is 1
Row 2 is 2
Row 3 is 3
Row 4 is 4
Row 5 is 5
Row 6 is 6
Row 7 is 7
Row 8 is 8
Row 9 is 9
Row 10 is 10

PL/SQL procedure successfully completed.

SQL> begin
  2    for r in (select * from (select rownum rn from dual connect by level < 11))
  3    loop
  4      dbms_output.put_line('rij '||r.rn||' is '||sql%rowcount);
  5    end loop;
  6  end;
  7  /
rij 1 is
rij 2 is
rij 3 is
rij 4 is
rij 5 is
rij 6 is
rij 7 is
rij 8 is
rij 9 is
rij 10 is
Re: How do I get the total row count within the cursor [message #243049 is a reply to message #242973] Tue, 05 June 2007 16:15 Go to previous messageGo to next message
haiza
Messages: 22
Registered: June 2007
Junior Member
Basically I'm trying to find if the current record is the last record in the cursor
Re: How do I get the total row count within the cursor [message #243050 is a reply to message #242971] Tue, 05 June 2007 16:23 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Basically I'm trying to find if the current record is the last record in the cursor
IMO, that is essentially unanswerable.
You only KNOW for sure any record is the "last record" after trying to get the "next" record & discovering that no more records exist.
By then it is no longer the "current" record but the most recent previous record.

[Updated on: Tue, 05 June 2007 16:23] by Moderator

Report message to a moderator

Re: How do I get the total row count within the cursor [message #243051 is a reply to message #243050] Tue, 05 June 2007 16:27 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
... and you'll get that one using the %NOTFOUND cursor attribute.
Re: How do I get the total row count within the cursor [message #243073 is a reply to message #243041] Tue, 05 June 2007 23:13 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Yes, the doc (PL/SQL User's Guide and Reference, Chapter 6 Performing SQL Operations from PL/SQL, section Managing Cursors in PL/SQL, subsection Implicit Cursors) stated:
Quote:
Implicit cursor attributes return information about the execution of DML and DDL statements, such INSERT, UPDATE, DELETE, SELECT INTO, COMMIT, or ROLLBACK statements. The cursor attributes are %FOUND, %ISOPEN %NOTFOUND, and %ROWCOUNT.

and
Quote:
%ROWCOUNT yields the number of rows affected by an INSERT, UPDATE, or DELETE statement, or returned by a SELECT INTO statement.

This seems to imply that it does not work for an implicit cursor loop.

Regards
Michel
Re: How do I get the total row count within the cursor [message #243115 is a reply to message #243073] Wed, 06 June 2007 02:37 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
You can use analytical function (lead) to see if your current record will be your last.
Re: How do I get the total row count within the cursor [message #243125 is a reply to message #243115] Wed, 06 June 2007 03:05 Go to previous messageGo to next message
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
If it doesn't slow down the query too much to use analytics you could also use count(*) over () to get the numer or rows.

select a.*, Count(*) over () 
  from (select rownum rn from dual connect by level < 11) a;
Re: How do I get the total row count within the cursor [message #243133 is a reply to message #243125] Wed, 06 June 2007 03:57 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Frank, Thomas,

Both queries will dramatically slow down the process as it forces Oracle to internally retrieve all the rows before giving the first one.

Regards
Michel
Re: How do I get the total row count within the cursor [message #243153 is a reply to message #243133] Wed, 06 June 2007 04:39 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
What makes you think adding a lead would force all rows to be retrieved? Is that because of the need for an order by?

Besides, functionality is more important than performance (IF the functionality really is needed)

Same functionality could be gained by storing the fetched row and handling the previous row in your cursor-for loop
Then after the loop handle the last row.

[Updated on: Wed, 06 June 2007 04:41]

Report message to a moderator

Re: How do I get the total row count within the cursor [message #243154 is a reply to message #243153] Wed, 06 June 2007 04:45 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
and how come this all of a sudden became a problem, 'dramatically slowing down the process'?
It didn't seem such a waste previously
Re: How do I get the total row count within the cursor [message #243159 is a reply to message #243153] Wed, 06 June 2007 05:05 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
What makes you think adding a lead would force all rows to be retrieved? Is that because of the need for an order by?

Yes.
Quote:
Besides, functionality is more important than performance (IF the functionality really is needed)

IF, yes.

Regards
Michel
Re: How do I get the total row count within the cursor [message #243160 is a reply to message #243154] Wed, 06 June 2007 05:08 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Frank wrote on Wed, 06 June 2007 11:45
and how come this all of a sudden became a problem, 'dramatically slowing down the process'?
It didn't seem such a waste previously

In this case, the question was really to search for next and previous row, it is the basic requested functionality.

In this case, it just seems to be an add-on.

Regards
Michel
Re: How do I get the total row count within the cursor [message #243183 is a reply to message #243160] Wed, 06 June 2007 06:35 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Which case is, actually, this one? /forum/fa/917/0/
Re: How do I get the total row count within the cursor [message #243184 is a reply to message #243183] Wed, 06 June 2007 06:50 Go to previous messageGo to next message
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
This one here, as opposed to this other one over there.

Which are not to be confused with that one here and that one over there under these other ones on the far side of the forum.

Unless you turn the whole thing around, then it is the same difference. (only upside down.) http://www.orafaq.com/forum/fa/917/0/

And now I'm getting so confused, that I start to see MS SQL Server ads at the top of the forum.
Re: How do I get the total row count within the cursor [message #243213 is a reply to message #243184] Wed, 06 June 2007 08:33 Go to previous message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Sometimes I should reread what I post. /forum/fa/1620/0/

Regards
Michel
Previous Topic: Diff Between 'Exist IN' and 'IN'
Next Topic: Spooling of Records
Goto Forum:
  


Current Time: Thu Dec 05 07:51:44 CST 2024