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

📖 Theory

The Blind Dashboard

Imagine driving a car where the windshield is completely blacked out, and you have to navigate purely by instructions fed over a radio. If you update the fine balances for overdue students in the CampusLib database, how do you know if your SQL statement actually changed 50 records, modified 1 record, or found absolutely nothing at all? Running database operations without status feedback is highly dangerous. To look inside the execution engine safely, PL/SQL provides four built-in system indicators called Cursor Attributes. They act like dashboard status lights, revealing exactly what happened during the last database transaction.

📖 Theory

The WhatsApp Read Receipt and the Odometer

Think of %FOUND and %NOTFOUND like WhatsApp delivery ticks. When you execute a fetch command, %FOUND turns into a bright blue double-tick, proving your programmatic hand successfully grabbed a real database row. If the stream dries up and you grab empty space, %NOTFOUND lights up instead. Meanwhile, think of %ROWCOUNT like a car's trip meter (odometer). It starts dead at zero the moment you open the data valve, and increments upward tick-by-tick (+1) for every single individual record row that passes through your loop filter.

📖 Theory

The Four System Monitors

PL/SQL tracks four fundamental properties for any cursor action. If you are inspecting an explicit cursor you built by hand, you attach these tokens directly to your custom cursor handle (e.g., c_stud_cursor%FOUND). If you want to check an implicit statement, like a standard standalone UPDATE, DELETE, or single-row SELECT INTO, you pair these tokens with the generic global prefix handle `SQL` (e.g., SQL%ROWCOUNT).

📊 At a glance

State matrix and datatype outcomes for PL/SQL cursor properties

⌨️ Practical

Auditing Rows Using Implicit and Explicit Status Hooks

-- An anonymous block capturing execution metrics using system attributes
DECLARE
  CURSOR c_defaulters IS
    SELECT member_id FROM members WHERE fine_balance > 200;
    
  v_id members.member_id%TYPE;
BEGIN
  -- 1. Testing Implicit Attributes on a DML Update
  UPDATE members 
  SET status = 'SUSPENDED' 
  WHERE fine_balance > 500;

  IF SQL%FOUND THEN
    DBMS_OUTPUT.PUT_LINE('Suspension applied! Rows impacted: ' || SQL%ROWCOUNT);
  ELSE
    DBMS_OUTPUT.PUT_LINE('No members crossed the high-fine threshold.');
  END IF;
  
  -- 2. Testing Explicit Attributes through a manual lifecycle
  OPEN c_defaulters;
  
  -- State Check right after opening
  IF c_defaulters%ISOPEN THEN
    DBMS_OUTPUT.PUT_LINE('Cursor open. Rows fetched so far: ' || c_defaulters%ROWCOUNT);
  END IF;

  LOOP
    FETCH c_defaulters INTO v_id;
    EXIT WHEN c_defaulters%NOTFOUND;
    
    DBMS_OUTPUT.PUT_LINE('Processing item #' || c_defaulters%ROWCOUNT || ' for ID: ' || v_id);
  END LOOP;
  
  CLOSE c_defaulters;
END;
/

🪜 Follow along

Timeline of Explicit Attribute State Transitions

1. 1. Prior to OPEN Evaluating %ISOPEN yields FALSE. Attempting to call %FOUND, %NOTFOUND, or %ROWCOUNT triggers an immediate ORA-01001 Invalid Cursor crash.
2. 2. Immediately Post-OPEN %ISOPEN flips to TRUE. %ROWCOUNT reads exactly 0. %FOUND and %NOTFOUND both report back as blank NULL states.
3. 3. Successful FETCH Cycles %FOUND evaluates to TRUE, %NOTFOUND becomes FALSE, and %ROWCOUNT ticks up by 1 for each record pulled into variables.
4. 4. Stream Exhaustion The pointer passes the final row. FETCH finds nothing. %FOUND shifts to FALSE, %NOTFOUND becomes TRUE, and %ROWCOUNT freezes at the final high score.
5. 5. Post-CLOSE Purge %ISOPEN drops back to FALSE. The other three properties immediately lose context and will throw exceptions if referenced again.

🧩 Quiz

What is the specific value of 'c_cursor%ROWCOUNT' immediately after an explicit cursor is opened, but right BEFORE the very first FETCH statement runs?

1. It returns NULL because no extraction attempt has been initiated yet.
2. It returns the total number of matching rows waiting in the active set collection.
3. It returns 0.
4. Oracle throws an ORA-01001 Invalid Cursor exception error state.

⚠️ Watch out

The Post-Close Memory Ghost Trap

This is an absolute favorite trick question for university theory exams! Students often write cleanup code like this: CLOSE c1; IF c1%NOTFOUND THEN.... This is a fatal logical trap! The absolute second you execute CLOSE c1;, the private workspace is wiped from memory and the cursor identity is destroyed. Asking a closed cursor for its %NOTFOUND state won't give you TRUE or FALSE, it immediately crashes your entire program runtime with an INVALID_CURSOR exception!

💭 Think first

The Empty Implicit Update Evaluation Check

Mental Challenge: If you execute an UPDATE query that matches zero rows in a table, what do 'SQL%NOTFOUND' and 'SQL%ROWCOUNT' evaluate to?

📖 Theory

University Lab Exam Scoring Strategy

When writing code for external laboratory evaluations, never use the explicit cursor name prefix when evaluating implicit statements. Writing IF c_my_cursor%FOUND for an anonymous standalone UPDATE command is an instant fail. For all standalone DML actions, always use the prefix SQL% directly to guarantee perfect performance marks from the evaluation panel.

✅ Summary

Key takeaways

• Cursor attributes are embedded status flags that return critical operational metrics about active statements.
• Use the explicit cursor name for manual pipelines, and the generic 'SQL' token for implicit updates/deletes.
• %ISOPEN checks for active memory residency; implicit statements are always FALSE because they auto-terminate.
• %FOUND and %NOTFOUND are binary validation indicators that remain completely NULL until a FETCH is executed.
• %ROWCOUNT is a running tally that tracks the volume of rows pulled out or modified by the database.
• Referencing attributes on a closed explicit cursor instantly breaks execution context with an Invalid Cursor error.
• Memory hook: %ISOPEN checks the line, %FOUND grabs the data, %NOTFOUND handles the exit, and %ROWCOUNT counts the pile!