Knowing the number of rows in a cursor
Hello everybody.
Can somebody tell me if there is a way to know the number of rows returned or included in a cursor without having to cross the entire result set ? a function like "resultSetCount" or something like that ? I thought that %rowcount could work, but this returns the current row number, and therefore I need to fetch all records until i get the last one.
I'm using Oracle 8i.
Thanks.
Software/Hardware used:
Software/Hardware used:
ASKED:
October 25, 2005 5:19 PM
UPDATED:
October 27, 2005 9:32 AM
Answer Wiki:
I doubt that such a function exists. What you want is essentially to retrieve count(*) in place of your selected column(s). Oracle appears to handle cursors very efficiently by not bringing back all of the rows at once, but only as it needs more to satisfy a fetch request. You could could do that "SELECT count(*)" with or without a cursor prior to fetching from your cursor, but it would take nearly as long as retrieving all of the rows from the cursor unless satisfying your WHERE clause did not require all of the table accesses (through use of indexes) needed to retrieve the data. Even then, you would only be skipping retrieval by row id. I sometimes prefer not to return any rows if there are far too many. If I don't consider it reasonable to deal with more than 50 rows, issuing the "SELECT count(*)" with the additional restriction on rownum in the WHERE clause gives me the information I need to skip the fetches from the cursor.
To see all answers submitted to the Answer Wiki: View Answer History.
I have solved this problem you face in the following way.
Bulk Fetch a cursor into a collection. in Oracle 8i, you need a separate array for each column, in 9i you can declare arrays of records.
Once the bulk fetch is executed, you simply reference the array.count attribute to get the count of the records processed by the cursor.
Example:
cursor MyCursor is select bla, bla, bla …..;
begin …
open MyCursor;
fetch MyCursor bulk collect into my_Array ;
close MyCursor ;
CursorRowCount := MyArray.count ;
Until Oracle has completed the processing of cursors and gerated the resultant dataset, there is no way for it to know how many rows it will process. To speed it up, you could try to create the cursor with the right hints and don’t forget to use bind variables if literals are part of the where clause.
Hope that helps. Regards:
Ferenc
Use the analytic form of the COUNT function in your current SELECT statement, like this:
SELECT COLA, COLB, COLC, COUNT(*) OVER (COLA) ROWS_RETURNED
FROM MY_TABLE
WHERE …
This will give you the total number of rows returned by the query in each row returned.
Agreeing with stevewaltz.
Oracle doesn’t know until it has fetched the last row.
The other replies will give you the count, but will also fetch all the rows.
Why do you want to know how many before hand ?
Ken
Agreeing with stevewaltz.
Oracle doesn’t know until it has fetched the last row.
The other replies will give you the count, but will also fetch all the rows.
Why do you want to know how many before hand ?
Ken
Thanks for all replies.
We have a heavy process, and the operator needs to know the progress, so, to calculate the progress % at any time, we need to know the number of rows that will be processed.
Regards,
Calculating the % complete value is trivially easy.
As I mentioned before, use the analytic form of the COUNT function to get this done:
select col_a, col_b, col_c,
(rownum / count(rownum) over ()) percent_done
from this_table
where …
This will give you a column called PERCENT_DONE in the select list which tells you the percentage of total records returned by the query that each row represents.
For example:
select state, state_name,
(rownum / count(rownum) over ()) percent
from states
/
ST STATE_NAME PERCENT
– —————————————- ———-
AA Armed Forces the Americassssssssssseww3s .016129032
AE Armed Forces Europe .032258065
AK Alaska .048387097
AL Alabama .064516129
AP Armed Forces Pacific .080645161
AR Arkansas .096774194
AS American Samoa .112903226
AZ Arizona .129032258
CA California .14516129
CO Colorado .161290323
CT Connecticut .177419355
DC District of Columbia .193548387
DE Delaware .209677419
FL Florida .225806452
FM Federated States of Micronesia .241935484
GA Georgia .258064516
GU Guam .274193548
HI Hawaii .290322581
IA Iowa .306451613
ID Idaho .322580645
IL Illinois .338709677
IN Indiana .35483871
KS Kansas .370967742
KY Kentucky .387096774
LA Louisiana .403225806
MA Massachusetts .419354839
MD Maryland .435483871
ME Maine .451612903
MH Marshall Islands .467741935
MI Michigan .483870968
MN Minnesota .5
MO Missouri .516129032
MP Northern Mariana Islands .532258065
MS Mississippi .548387097
MT Montana .564516129
NC North Carolina .580645161
ND North Dakota .596774194
NE Nebraska .612903226
NH New Hampshire .629032258
NJ New Jersey .64516129
NM New Mexico .661290323
NV Nevada .677419355
NY New York .693548387
OH Ohio .709677419
OK Oklahoma .725806452
OR Oregon .741935484
PA Pennsylvania .758064516
PR Puerto Rico .774193548
RI Rhode Island .790322581
SC South Carolina .806451613
SD South Dakota .822580645
TN Tennessee .838709677
TX Texas .85483871
UT Utah .870967742
VA Virginia .887096774
VI Virgin Islands, U.S .903225806
VT Vermont .919354839
WA Washington .935483871
WI Wisconsin .951612903
WV West Virginia .967741935
WY Wyoming .983870968
ZZ Zzyzyva 1
62 rows selected.
This works properly even if you have a where clause. It gives the percentage of the records the query will return.