Q
Problem solve Get help with specific problems with your technologies, process and projects.

Determining total number of records in a cursor

Published: 12 Jul 2002

I would like to know if there is any way of finding out the number of records present in an open cursor. The rowcount...

gives only the number of rows fetched. I want to know the total records that are present in the cursor before I actually start fetching the records from the cursor.

To get the number of records that the cursor will process do a SELECT statement using COUNT(*) which mimics your cursor SELECT statement prior to opening the cursor.

For example:
If your cursor statement is

  SELECT cust_id, lastname, discount_pct
    FROM customer
   WHERE state = v_state;
Then you'd get the count of records with
  SELECT count(*)
    INTO ctr_variable
    FROM customer
   WHERE state = v_state;

For More Information


Make sure you have a "good reason" for doing this. If you intend to process all the records in the cursor regardless of the count, then the count you do before hand is just adding overhead/inefficiency to your code. Avoid doing counts unless you really need them because if you have a really big table, the count can take quite a bit of time (relatively speaking).

Dig Deeper on Oracle and SQL

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close