Explicit Cursor Life Cycle FAQS

1. What is an explicit cursor in Oracle?

An explicit cursor is a cursor declared and controlled by the programmer to process multiple rows from a query.

• Unlike implicit cursors, you must declare, open, fetch, and close it manually.
• It gives full control over row-by-row processing.

2. What are the stages in the life cycle of an explicit cursor?

The explicit cursor life cycle has four main stages:

Stage	Description
Declaration	Cursor is declared with a SELECT statement in the DECLARE section.
Opening	Cursor is opened using OPEN cursor_name, parsing and executing the query.
Fetching	Rows are fetched one by one into variables using FETCH cursor_name INTO ....
Closing	Cursor is closed using CLOSE cursor_name to release resources.

3. How does the life cycle work with an example?

DECLARE

   CURSOR emp_cursor IS

      SELECT employee_id, first_name

      FROM employees

      WHERE department_id = 10;

 

   v_id employees.employee_id%TYPE;

   v_name employees.first_name%TYPE;

 

BEGIN

   OPEN emp_cursor;

 

   LOOP

      FETCH emp_cursor INTO v_id, v_name;

      EXIT WHEN emp_cursor%NOTFOUND;

      DBMS_OUTPUT.PUT_LINE(v_id || ' - ' || v_name);

   END LOOP;

 

   CLOSE emp_cursor;

END;

4. What are the cursor attributes used in the life cycle?

Attribute	Description	When to Use
%FOUND	TRUE if the last FETCH returned a row	After FETCH
%NOTFOUND	TRUE if the last FETCH did not return a row	After FETCH
%ROWCOUNT	Number of rows fetched so far	After FETCH
%ISOPEN	TRUE if cursor is open, FALSE if closed	Before/After CLOSE

5. Can you fetch multiple rows at once?

Yes, using BULK COLLECT:

DECLARE

   TYPE emp_table IS TABLE OF employees.employee_id%TYPE;

   v_emp_ids emp_table;

BEGIN

   OPEN emp_cursor;

   FETCH emp_cursor BULK COLLECT INTO v_emp_ids;

   CLOSE emp_cursor;

   DBMS_OUTPUT.PUT_LINE('Rows fetched: ' || v_emp_ids.COUNT);

END;

• Improves performance for large data sets.
• Part of the explicit cursor life cycle: fetch step is bulked.

6. What happens if you forget to close a cursor?

• Memory remains allocated for the cursor
• Could lead to “maximum open cursors exceeded” error
• Always use CLOSE at the end of cursor processing

7. Can explicit cursors be parameterized?

Yes. You can pass parameters when opening a cursor:

DECLARE

   CURSOR emp_cursor (p_dept NUMBER) IS

      SELECT employee_id, first_name

      FROM employees

      WHERE department_id = p_dept;

 

BEGIN

   OPEN emp_cursor(20);

END;

8. Difference between explicit and implicit cursor life cycle

Feature	Implicit Cursor	Explicit Cursor
Created by	Oracle	Programmer
Declaration	Not required	Required
OPEN	Automatic	Manual
FETCH	Automatic (single-row)	Manual or BULK
CLOSE	Automatic	Manual
Multi-row support	No	Yes
Cursor attributes	SQL%FOUND, SQL%ROWCOUNT	cursor_name%FOUND, cursor_name%ROWCOUNT, cursor_name%ISOPEN

9. Common mistakes in explicit cursor life cycle

• Forgetting to OPEN before FETCH
• Not including EXIT WHEN cursor%NOTFOUND → infinite loop
• Forgetting to CLOSE → memory/resource leaks
• Fetch variable types mismatch with SELECT columns

10. Best practices

• Prefer Cursor FOR Loops when you don’t need manual control
• Always CLOSE cursors to release resources
• Use parameterized cursors for flexibility
• Use BULK COLLECT for large result sets
• Check %ROWCOUNT to ensure all expected rows were processed