Determining total number of records in a cursor
I would like to know if there is any way of finding out the number of records present in an open cursor. The rowcount...
Continue Reading This Article
Enjoy this article as well as all of our content, including E-Guides, news, tips and more.
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
- Dozens more answers to tough Oracle questions from Karen Morton are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.