Home » SQL & PL/SQL » SQL & PL/SQL » How to find the no of records selected by a curcor before processing the rows? (9i)
How to find the no of records selected by a curcor before processing the rows? [message #289789] Wed, 26 December 2007 02:34 Go to next message
vanathi
Messages: 74
Registered: December 2007
Location: India
Member
Hi,

I need to find the no of records selected by the cursor before using them...please help me...


thanks in advance

with regards,
vanathi
Re: How to find the no of records selected by a curcor before processing the rows? [message #289792 is a reply to message #289789] Wed, 26 December 2007 02:48 Go to previous messageGo to next message
spmano1983
Messages: 269
Registered: September 2007
Senior Member
Hi,

You can use cursor_name%rowcount;

If anything wrong let me know.

Mano
Re: How to find the no of records selected by a curcor before processing the rows? [message #289801 is a reply to message #289792] Wed, 26 December 2007 03:03 Go to previous messageGo to next message
vanathi
Messages: 74
Registered: December 2007
Location: India
Member
I am using it in procedure.

cursor decalartion
begin
dbms_output.put_line('hi'|| PerformanceCur%rowcount);

this statement thorws exception
Re: How to find the no of records selected by a curcor before processing the rows? [message #289811 is a reply to message #289789] Wed, 26 December 2007 03:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can't have the number of rows a query will return BEFORE fetching these rows.

Regards
Michel
Re: How to find the no of records selected by a curcor before processing the rows? [message #289812 is a reply to message #289811] Wed, 26 December 2007 03:30 Go to previous messageGo to next message
vanathi
Messages: 74
Registered: December 2007
Location: India
Member
please tell me...
can I use the print line
after the fetching loop?
Re: How to find the no of records selected by a curcor before processing the rows? [message #289817 is a reply to message #289789] Wed, 26 December 2007 03:41 Go to previous messageGo to next message
spmano1983
Messages: 269
Registered: September 2007
Senior Member
Hi you can use this but you can get 0 only,

Because after fetching data only you can exact rowcount.

declare
cursor c1 is select * from emp;
begin
open c1;
dbms_output.put_line(c1%rowcount);
end;

Sorry for before said wrong things.


Thanks
Manohar
Re: How to find the no of records selected by a curcor before processing the rows? [message #289818 is a reply to message #289812] Wed, 26 December 2007 03:41 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Yes you can.

SQL> set serverout on
SQL>
SQL> DECLARE
  2     r_emp employees%ROWTYPE;
  3     CURSOR c_emps
  4     IS
  5     SELECT *
  6     FROM   employees;
  7
  8  BEGIN
  9    OPEN c_emps;
 10    LOOP
 11       EXIT WHEN c_emps%NOTFOUND;
 12       FETCH c_emps INTO r_emp;
 13    END LOOP;
 14    dbms_output.put_line('Number of records: '||c_emps%ROWCOUNT);
 15    CLOSE c_emps;
 16  END;
 17  /
Number of records: 107

PL/SQL procedure successfully completed.


MHE
Re: How to find the no of records selected by a curcor before processing the rows? [message #289819 is a reply to message #289812] Wed, 26 December 2007 03:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, you can if you sum the rowcount at each loop.
By the way, if you fetch one row at a time (which is a bad practice) you just have to count the number of iterations in your loop.

Regards
Michel
Re: How to find the no of records selected by a curcor before processing the rows? [message #289820 is a reply to message #289789] Wed, 26 December 2007 03:47 Go to previous messageGo to next message
vanathi
Messages: 74
Registered: December 2007
Location: India
Member
Thank you friends


Smile
Re: How to find the no of records selected by a curcor before processing the rows? [message #289830 is a reply to message #289811] Wed, 26 December 2007 04:21 Go to previous messageGo to next message
vanathi
Messages: 74
Registered: December 2007
Location: India
Member
Sorry friends.
Still I am getting the same error

Declaration

begin
loop

endloop
dbms line

it shows error in dbms line.

Sad
Re: How to find the no of records selected by a curcor before processing the rows? [message #289832 is a reply to message #289830] Wed, 26 December 2007 04:26 Go to previous messageGo to next message
babuknb
Messages: 1734
Registered: December 2005
Location: NJ
Senior Member

Michel said clearly. I think problem in your code.

Can you post your exact code.

Babu
Re: How to find the no of records selected by a curcor before processing the rows? [message #289837 is a reply to message #289789] Wed, 26 December 2007 04:36 Go to previous messageGo to next message
vanathi
Messages: 74
Registered: December 2007
Location: India
Member
Michel's method will work definitely.
But I tried Maaher's idea.
I am getting 'cursor already opened' exception.
Re: How to find the no of records selected by a curcor before processing the rows? [message #289842 is a reply to message #289837] Wed, 26 December 2007 04:44 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
The principle is:
- open the cursor ONCE
- fetch until you get a cursor%NOTFOUND
- display cursor%ROWCOUNT (or put that in a variable)
- close the cursor

MHE
Re: How to find the no of records selected by a curcor before processing the rows? [message #289854 is a reply to message #289789] Wed, 26 December 2007 05:05 Go to previous messageGo to next message
vanathi
Messages: 74
Registered: December 2007
Location: India
Member
Thank you Maaher.

why I tried to get this number is,
I tried to load a table from a core table.
I am selecting the values from core table using cursor.
But the execution time differs even the same no of records processed.


SQL> begin
2 LOAD_CORE_PERFORMANCE;
3 end;
4 /
hi23906

PL/SQL procedure successfully completed.

Elapsed: 00:04:52.03
SQL> /
hi23916

PL/SQL procedure successfully completed.

Elapsed: 00:05:51.08
SQL> /
hi23916

PL/SQL procedure successfully completed.

Elapsed: 00:07:15.00

the number with hi is the number of cursor rows.
Even this numbers are same. It takes different time.
Please help me.
Re: How to find the no of records selected by a curcor before processing the rows? [message #289855 is a reply to message #289854] Wed, 26 December 2007 05:06 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
So now we're talking about performance all of the sudden? Confused

MHE
Re: How to find the no of records selected by a curcor before processing the rows? [message #289857 is a reply to message #289789] Wed, 26 December 2007 05:09 Go to previous messageGo to next message
vanathi
Messages: 74
Registered: December 2007
Location: India
Member
Yes Friend.

evrytime I am getting the same data.
once the core table is loaded, I ll load the final table.
In that SQL,
I loaded the final 3 times.
every time the core has different datas.
Re: How to find the no of records selected by a curcor before processing the rows? [message #289858 is a reply to message #289854] Wed, 26 December 2007 05:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
For performances, first read How to Identify Performance Problem and Bottleneck and the links it contain.
Also read OraFAQ Forum Guide to know how to format your post and what are the data you have to provide if you want us help you.

Regards
Michel
Re: How to find the no of records selected by a curcor before processing the rows? [message #289859 is a reply to message #289857] Wed, 26 December 2007 05:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

every time the core has different datas.

Different data, different query, different result set, different access path, why do you want same time?

Regards
Michel
Re: How to find the no of records selected by a curcor before processing the rows? [message #289865 is a reply to message #289789] Wed, 26 December 2007 05:29 Go to previous message
vanathi
Messages: 74
Registered: December 2007
Location: India
Member
Thank you for your replies.

I will format my post and post it tomorrow.

Smile
Previous Topic: Clarification regarding REGEXP functions
Next Topic: nested tables
Goto Forum:
  


Current Time: Sun Dec 11 08:16:09 CST 2016

Total time taken to generate the page: 0.08386 seconds