Cursor Attributes: %FOUND, %NOTFOUND, %ISOPEN, %ROWCOUNT

📖 Theory

Checking the heartbeat of your query

Imagine you run a loop to fetch rows from TicketDesk that need urgent attention. How does your PL/SQL program actually know when the last ticket has been pulled? What happens if you try to fetch from a cursor that you forgot to open, or how do you display the total number of records processed so far? You cannot look inside the database memory with your naked eyes. You need a set of built-in indicators that instantly reveal the state of your cursor at any given millisecond.

📖 Theory

The Car Dashboard Indicators

Think of a cursor like driving a delivery van. You cannot see inside the engine or the fuel tank directly while moving. Instead, you look at your dashboard. The fuel gauge tells you if there is petrol left (%FOUND or %NOTFOUND). The odometer tells you exactly how many kilometers you have traveled (%ROWCOUNT). The ignition light tells you if the engine is running or completely switched off (%ISOPEN). These dashboard metrics give you live feedback without forcing you to dismantle the vehicle.

📖 Theory

Cursor Attributes in Oracle PL/SQL

In the Oracle database dialect, cursor attributes are system-defined properties that act as status flags for a cursor. Every time you execute a SQL statement or perform a cursor operation, Oracle automatically updates these attributes in the private memory area. By appending an attribute name to your cursor variable using the percentage symbol (%), your procedural code can inspect runtime properties like row counts and fetch success to make dynamic execution choices.

📊 At a glance

Core cursor status attributes in the Oracle PL/SQL programming engine

⌨️ Practical

Tracking Ticket Audits with Attributes

-- Enable console output in Oracle environment
SET SERVEROUTPUT ON;

DECLARE
  CURSOR c_high_priority IS
    SELECT id, title FROM tickets WHERE priority = 'High';
    
  v_id tickets.id%TYPE;
  v_title tickets.title%TYPE;
BEGIN
  -- Check if cursor is already active before opening
  IF NOT c_high_priority%ISOPEN THEN
    OPEN c_high_priority;
  END IF;
  
  LOOP
    FETCH c_high_priority INTO v_id, v_title;
    
    -- Stop loop automatically when no rows remain
    EXIT WHEN c_high_priority%NOTFOUND;
    
    DBMS_OUTPUT.PUT_LINE('Fetched row number ' || c_high_priority%ROWCOUNT || ': ' || v_title);
  END LOOP;
  
  DBMS_OUTPUT.PUT_LINE('Total tickets processed: ' || c_high_priority%ROWCOUNT);
  CLOSE c_high_priority;
END;
/

🧩 Quiz

Suppose an explicit cursor has retrieved 5 rows successfully during a loop execution. What will the value of %ROWCOUNT be immediately after the cursor is explicitly closed?

1. 5
2. 0
3. NULL
4. Triggers an INVALID_CURSOR exception

⚠️ Watch out

The Implicit vs Explicit %NOTFOUND Trap

A classic mistake in Indian BCA university laboratory practicals is using the wrong cursor prefix. If you are checking an explicit cursor named c_tickets, you must use c_tickets%NOTFOUND. If you mistakenly write SQL%NOTFOUND, Oracle will check the status of the last implicit SQL query instead of your explicit cursor! This causes loops to execute incorrectly or run infinitely because you are querying the wrong dashboard indicator.

💭 Think first

Mental Challenge: The Initial State of %ROWCOUNT

Before any FETCH statement executes but right after an explicit cursor is successfully opened, what numeric value do you think %ROWCOUNT holds? Work out the answer mentally before tapping.

📖 Theory

Database Audits and Future Synchronization

Cursor attributes are vital when generating detailed management reports or tracking batch processing statistics in production helpdesk environments. You will rely heavily on row-counting patterns next semester in your Sem 3 BCA303 SQLite mobile database course. When sync routines fetch updates from a remote master server, your code will use local loop counts to paint progress percentages onto the mobile user interface screen.

✅ Summary

Key takeaways

• Cursor attributes are specialized boolean or numeric flags that describe execution state.
• Use %ISOPEN to verify if a cursor is active before running an OPEN or CLOSE command.
• The %FOUND and %NOTFOUND flags indicate whether the last FETCH operation successfully returned data.
• The %ROWCOUNT attribute tracks the cumulative number of rows processed since the cursor opened.
• Attempting to reference attributes on a closed explicit cursor results in an immediate exception error.
• Memory hook: Open with ISOPEN, test with NOTFOUND, count with ROWCOUNT, and check before you close!