Declare, open, fetch and close cursors

📖 Theory

The Overdue-List Walk

Imagine your university librarian hands you a sheet of paper containing the names of 45 students who haven't returned their programming textbooks. Your task is to look at the first name, find their phone number, print a warning note, cross them off, and slide down to the next row. You repeat this sequence manually until you run out of rows. In PL/SQL, if a query returns multiple records, the computer can't process them all simultaneously in a single flash. We have to explicitly guide the database engine through these exact physical steps: pointing to the data, loading it, grabbing one line at a time, and cleaning up the workspace.

📖 Theory

The Underground Water Pipeline

Think of managing an explicit cursor like setting up an agricultural borewell pipeline on a farm. First, you lay down the blueprint mapping exactly where the underground water channel runs (DECLARE). Next, you crank open the main valve, causing water to surge up into your local holding tank (OPEN). Then, you fill your buckets one by one from the tap to water your crops sequentially (FETCH). Finally, when the fields are fully watered, you tightly lock the valve to save electricity and prevent system flooding (CLOSE).

📖 Theory

The Four Commandments of Explicit Control

To safely handle multi-row outputs without crashing your code with a TOO_MANY_ROWS exception, you must take complete programmatic ownership of the cursor's life cycle. This lifecycle is broken into four distinct operational commands written across your PL/SQL block structure.

📊 At a glance

The systematic architectural journey of a programmer-managed explicit cursor

⌨️ Practical

Stepping Through the Library Overdue Ledger

-- An anonymous block to manually fetch and display high-fine library members
DECLARE
  -- Step 1: Declare the cursor with its query blueprint
  CURSOR c_fine_ledger IS
    SELECT member_id, fine_balance 
    FROM members 
    WHERE fine_balance > 150;
    
  -- Variables to receive cursor values during FETCH
  v_mem_id   members.member_id%TYPE;
  v_balance  members.fine_balance%TYPE;
BEGIN
  -- Step 2: Open the data channel and freeze the active set
  OPEN c_fine_ledger;
  
  -- Iterating through rows explicitly
  LOOP
    -- Step 3: Pull data into target variables
    FETCH c_fine_ledger INTO v_mem_id, v_balance;
    
    -- Universal guard rule: exit early when no more rows exist
    EXIT WHEN c_fine_ledger%NOTFOUND;
    
    -- Operational logic on the current row
    DBMS_OUTPUT.PUT_LINE('Alert sent to Member ID: ' || v_mem_id || ' | Penalty: Rs. ' || v_balance);
  END LOOP;
  
  -- Step 4: Shut down the valve and reclaim RAM
  CLOSE c_fine_ledger;
END;
/

🪜 Follow along

Tracer Bullet: The Row Pointer's Movement

1. 1. The OPEN Ignition The query finds 3 matching rows. The database engine creates a memory block holding these 3 entries. The pointer rests at position 'Zero', just above row 1.
2. 2. First FETCH Strike Oracle reads Row 1, copies its data into 'v_mem_id' and 'v_balance', and instantly pushes the pointer down to rest directly on Row 2.
3. 3. The End-of-Data Signal During the fourth loop iteration, FETCH tries to read data, but the pointer hits empty space past Row 3. The internal system flag %NOTFOUND flips immediately from FALSE to TRUE.
4. 4. Clean Evacuation The EXIT WHEN rule catches the TRUE flag, breaks out of the loop wheel instantly, and routes control to the CLOSE statement to purge the 3-row memory cache.

🧩 Quiz

What happens if a developer attempts to execute a FETCH statement on a named explicit cursor before executing its matching OPEN statement?

1. The block executes successfully but all target variables are loaded with NULL values.
2. Oracle automatically opens the cursor implicitly behind the scenes to safeguard execution.
3. The PL/SQL engine encounters a runtime crash throwing an immediate 'INVALID_CURSOR' (ORA-01001) exception.
4. The compiler catches the sequence during compilation and flags an immediate syntax breakdown.

⚠️ Watch out

The Infinite Duplication Trap

Pay close attention to where you place your EXIT WHEN safety valve! A classic mistake made by university students in practical exams is placing the exit condition before the FETCH statement inside the loop. If you check %NOTFOUND right at the start of the loop before any extraction occurs, the attribute evaluates to FALSE (or NULL), meaning the loop will pass. On the final round, it can end up printing the very last row's data twice because the program evaluates the old variables before realizing the data stream has dried up!

💭 Think first

The Structural Variable Alignment Check

If your cursor SELECT statement extracts exactly three columns (e.g., id, name, fine), but your FETCH code states 'FETCH c1 INTO v_id, v_name;', what will Oracle do? Think about structural assignment symmetry.

📖 Theory

Lab Exam Score Maximizer

When an external university laboratory examiner asks you to show an explicit cursor on screen, always double-check that your CLOSE command sits safely outside the loop body, but right before the block's final END; statement. Leaving cursors unclosed is the easiest way to lose performance marks for careless memory management habits.

✅ Summary

Key takeaways

• Explicit cursors are custom, programmatic row-by-row data pipelines managed manually by developers.
• The DECLARE step sets up the target query blueprint without tracking actual records or allocating row RAM.
• The OPEN step runs the underlying query, builds the active set context area, and initializes the row pointer.
• The FETCH step pulls data from the active record into local variables and advances the pointer downward.
• The CLOSE step safely terminates the pipeline, releasing memory buffers back to the database engine.
• Failing to track state changes using the %NOTFOUND attribute can result in infinite loops or duplicate record printing.
• Memory hook: Map it out (DECLARE), switch it on (OPEN), milk the rows (FETCH), and seal the valve (CLOSE)!