Knowing the number of rows in a cursor

70200 pts.
Tags:
Database
IBM DB2
Microsoft Windows
Oracle
SQL
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

Thanks. We'll let you know when a new response is added.

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.

Discuss This Question: 6  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
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
  • FerencMantfeld
    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
    0 pointsBadges:
    report
  • Jolora
    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.
    0 pointsBadges:
    report
  • Bozzo999
    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
    0 pointsBadges:
    report
  • Bozzo999
    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
    0 pointsBadges:
    report
  • carlosdl
    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,
    70,200 pointsBadges:
    report
  • Jolora
    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.
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

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

REGISTER or login:

Forgot Password?
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

Thanks! We'll email you when relevant content is added and updated.

Following