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.

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.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States.
Privacy

Processing your response...

Discuss This Question: 6 &nbspReplies

There was an error processing your information. Please try again later.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States.
Privacy

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,

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States.
Privacy

Processing your reply...

Ask a Question

Free Guide: Managing storage for virtual environments

Complete a brief survey to get a complimentary 70-page whitepaper featuring the best methods and solutions for your virtual environment, as well as hypervisor-specific management advice from TechTarget experts. Don’t miss out on this exclusive content!

To follow this tag...

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States.
Privacy