| Cursor record counts [message #2918] |
Wed, 21 August 2002 08:59  |
Mike Nibeck
Messages: 49
Registered: May 2002
|
Member |
|
|
Env: Oracle 8i (UNIX)
.NET Client code
We have a stored proc that performs a SELECT on an output cursor. We then pass the cursor back to the calling .NET code for it to do with as it pleases.
Now, we want to pass back a record count of the number of records in the cursor.
Problems:
1) If I count the records in the cursoor (FETCH...%ROWCOUNT), the count goes back, but the cursor is emplty.
2) If they perform the count, they have to read all of the records FIRST before they can get a count. This is ceratinly counter-intuitive.
The client want to be able to tell the user to refine their search patterns if the current result set is over 'n' records long.
Any ideas?
Is their a way to count the number of rows in a cursor and keep the cursor viable?
_mike
|
|
|
|
| Re: Cursor record counts [message #2920 is a reply to message #2918] |
Wed, 21 August 2002 10:42  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
Mike, it's a common question, but there is no way to get a count without fetching from the cursor, so here are a couple of options:
1) Add a 'select count(*)' prior to the cursor open that counts the rows in the result set. This is usually not preferable because of the performance hit. You can add a 'where rownum <= p_upper_limit' to potentially speed up the check a bit (you only need to know if the count if above or below your limit):
select count(*)
into v_count
from ...
where rownum <= p_upper_limit;
if v_count < p_upper_limit then
open p_rc for
select ...;
end if;
v_count also gets returned as an output parameter that is reviewed in the calling program and the cursor fetch is not initiated if the count exceeds the limit.
2) Skip the pre-count and add a 'where rownum <= p_upper_limit' clause to your cursor open insuring that the result set will never exceed that preset limit.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
