Oracle SQL Experts: Oracle Explicit Cursor Faqs With Examples

1. What is an Explicit Cursor?

An explicit cursor is a cursor that is declared and controlled by the programmer in PL/SQL. It is mainly used when a query returns multiple rows and you want to process those rows one by one.

Basic flow:

DECLARE
   ↓
OPEN
   ↓
FETCH
   ↓
PROCESS
   ↓
CLOSE

Example:

DECLARE
    CURSOR emp_cursor IS
        SELECT employee_id, first_name
        FROM employees;


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;
/

2. Why do we use Explicit Cursors?

Explicit cursors are useful when you need to process multiple rows one by one, apply different logic to individual rows, perform calculations for each row, update or process data based on each fetched record, or have more control over the cursor lifecycle.

3. What are the steps of an Explicit Cursor?

There are traditionally four steps: DECLARE, OPEN, FETCH, and CLOSE.

Easy memory trick:

DOFC = Declare → Open → Fetch → Close

4. How do you declare an Explicit Cursor?

Syntax:

CURSOR cursor_name IS
    SELECT_statement;

Example:

DECLARE
    CURSOR emp_cursor IS
        SELECT employee_id, first_name, salary
        FROM employees;
BEGIN
    NULL;
END;
/

5. What is the OPEN operation?

OPEN activates the explicit cursor and makes its result set available for fetching.

OPEN emp_cursor;

6. What is FETCH?

FETCH retrieves the next row from the cursor result set into variables or a record.

FETCH emp_cursor
INTO v_id, v_name, v_salary;

7. What is CLOSE?

CLOSE closes the cursor after processing is complete.

CLOSE emp_cursor;

8. Complete Explicit Cursor Example

DECLARE
    CURSOR emp_cursor IS
        SELECT employee_id, first_name, salary
        FROM employees;


v_id     employees.employee_id%TYPE;
v_name   employees.first_name%TYPE;
v_salary employees.salary%TYPE;


BEGIN
OPEN emp_cursor;


LOOP
    FETCH emp_cursor
    INTO v_id, v_name, v_salary;

    EXIT WHEN emp_cursor%NOTFOUND;

    DBMS_OUTPUT.PUT_LINE(
        v_id || ' - ' ||
        v_name || ' - ' ||
        v_salary
    );
END LOOP;

CLOSE emp_cursor;


END;
/

Execution flow:

DECLARE cursor
      ↓
OPEN cursor
      ↓
FETCH row 1
      ↓
Process row 1
      ↓
FETCH row 2
      ↓
Process row 2
      ↓
...
      ↓
%NOTFOUND = TRUE
      ↓
EXIT LOOP
      ↓
CLOSE cursor

9. What are Explicit Cursor Attributes?

Oracle provides four important attributes:

cursor_name%FOUND
cursor_name%NOTFOUND
cursor_name%ROWCOUNT
cursor_name%ISOPEN

10. What is %FOUND?

%FOUND tells whether the most recent fetch successfully retrieved a row.

Example:

DECLARE
    CURSOR emp_cursor IS
        SELECT employee_id, first_name
        FROM employees;


v_id   employees.employee_id%TYPE;
v_name employees.first_name%TYPE;


BEGIN
OPEN emp_cursor;


FETCH emp_cursor INTO v_id, v_name;

IF emp_cursor%FOUND THEN
    DBMS_OUTPUT.PUT_LINE('Row found');
END IF;

CLOSE emp_cursor;


END;
/

11. What is %NOTFOUND?

%NOTFOUND becomes TRUE when the most recent FETCH does not retrieve a row. It is commonly used to stop a cursor loop.

LOOP
    FETCH emp_cursor INTO v_id, v_name;

    EXIT WHEN emp_cursor%NOTFOUND;

    DBMS_OUTPUT.PUT_LINE(v_name);
END LOOP;

Important: %NOTFOUND is commonly used with EXIT WHEN in explicit cursor loops.

12. What is %ROWCOUNT?

%ROWCOUNT tells how many rows have been fetched so far.

FETCH emp_cursor INTO v_id, v_name;

DBMS_OUTPUT.PUT_LINE(
    'Rows fetched: ' || emp_cursor%ROWCOUNT
);

After the first fetch, the value is 1; after the second fetch, it is 2; and so on.

13. What is %ISOPEN?

%ISOPEN tells whether an explicit cursor is currently open.

OPEN emp_cursor;

IF emp_cursor%ISOPEN THEN
    DBMS_OUTPUT.PUT_LINE('Cursor is open');
END IF;

CLOSE emp_cursor;

After CLOSE, emp_cursor%ISOPEN = FALSE.

14. Explicit Cursor Attributes — Summary

Attribute	Meaning
%FOUND	Last fetch successfully found a row
%NOTFOUND	Last fetch did not find a row
%ROWCOUNT	Number of rows fetched so far
%ISOPEN	Whether cursor is open

15. Why is %NOTFOUND important?

When there are no more rows, %NOTFOUND becomes TRUE and the loop exits.

FETCH
 ↓
No row
 ↓
%NOTFOUND = TRUE
 ↓
EXIT

16. What is the correct position of %NOTFOUND?

The common pattern is to check it after the FETCH.

LOOP
    FETCH emp_cursor INTO v_id, v_name;

    EXIT WHEN emp_cursor%NOTFOUND;

    -- Process fetched row
END LOOP;

17. Can an Explicit Cursor return multiple rows?

Yes. This is one of its main uses.

18. Can an Explicit Cursor return zero rows?

Yes. If the query returns no rows, the cursor can still be opened, and the first fetch will indicate that no row was returned.

19. Explicit Cursor with WHERE Condition

DECLARE
    CURSOR emp_cursor IS
        SELECT employee_id, first_name, salary
        FROM employees
        WHERE department_id = 10;
BEGIN
    FOR emp IN emp_cursor LOOP
        DBMS_OUTPUT.PUT_LINE(
            emp.employee_id || ' - ' || emp.first_name
        );
    END LOOP;
END;
/

20. What is a Parameterized Explicit Cursor?

A parameterized cursor accepts parameters when it is opened or used.

DECLARE
    CURSOR emp_cursor(p_dept_id NUMBER) IS
        SELECT employee_id, first_name
        FROM employees
        WHERE department_id = p_dept_id;

    v_id   employees.employee_id%TYPE;
    v_name employees.first_name%TYPE;
BEGIN
    OPEN emp_cursor(10);

    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;
/

21. Why use a Parameterized Cursor?

It allows the same cursor definition to be reused with different values.

22. Can we create multiple Explicit Cursors?

Yes. Each cursor can have its own query and lifecycle.

23. What is a Cursor FOR LOOP?

A Cursor FOR LOOP is a convenient way to process cursor rows. Oracle/PLSQL automatically handles OPEN, FETCH, and CLOSE for the loop.

DECLARE
    CURSOR emp_cursor IS
        SELECT employee_id, first_name
        FROM employees;
BEGIN
    FOR emp IN emp_cursor LOOP
        DBMS_OUTPUT.PUT_LINE(
            emp.employee_id || ' - ' || emp.first_name
        );
    END LOOP;
END;
/

24. Do we need OPEN, FETCH, and CLOSE with a Cursor FOR LOOP?

No. The Cursor FOR LOOP automatically handles the cursor lifecycle.

25. Explicit Cursor vs Cursor FOR LOOP

Manual Explicit Cursor	Cursor FOR LOOP
OPEN emp_cursor	No OPEN needed
FETCH emp_cursor INTO ...	No FETCH needed
EXIT WHEN emp_cursor%NOTFOUND	No EXIT WHEN needed
CLOSE emp_cursor	No CLOSE needed

26. Can we use a query directly in a Cursor FOR LOOP?

Yes. You do not even need to declare a named cursor.

BEGIN
    FOR emp IN (
        SELECT employee_id, first_name
        FROM employees
    )
    LOOP
        DBMS_OUTPUT.PUT_LINE(
            emp.employee_id || ' - ' || emp.first_name
        );
    END LOOP;
END;
/

27. What happens if you FETCH from a closed cursor?

Oracle raises a cursor-related error such as INVALID_CURSOR.

28. What happens if you OPEN an already open cursor?

An already open explicit cursor cannot be opened again without first closing it. This causes an error such as INVALID_CURSOR.

29. What happens if you CLOSE an already closed cursor?

Attempting to close an already closed cursor raises a cursor error such as INVALID_CURSOR.

30. What happens if the cursor query returns no rows?

The cursor can be opened, but the fetch indicates that no row was returned.

31. Explicit Cursor with Record

DECLARE
    CURSOR emp_cursor IS
        SELECT employee_id, first_name, salary
        FROM employees;


v_emp emp_cursor%ROWTYPE;


BEGIN
OPEN emp_cursor;


LOOP
    FETCH emp_cursor INTO v_emp;
    EXIT WHEN emp_cursor%NOTFOUND;

    DBMS_OUTPUT.PUT_LINE(
        v_emp.employee_id || ' - ' ||
        v_emp.first_name || ' - ' ||
        v_emp.salary
    );
END LOOP;

CLOSE emp_cursor;


END;
/

32. What is %ROWTYPE with a Cursor?

cursor_name%ROWTYPE creates a record that can hold one row returned by the cursor.

33. Can an Explicit Cursor be used for UPDATE?

Yes. You can fetch rows and then perform operations based on the fetched values.

34. What is FOR UPDATE in an Explicit Cursor?

FOR UPDATE can be used when you intend to update or delete the rows selected by the cursor.

35. What is WHERE CURRENT OF?

WHERE CURRENT OF allows you to update or delete the current row of a cursor declared with FOR UPDATE.

Easy memory trick:

FOR UPDATE
    ↓
Lock selected rows for update
    ↓
WHERE CURRENT OF
    ↓
Modify current cursor row

36. Explicit Cursor vs Implicit Cursor

Explicit Cursor	Implicit Cursor
Programmer declares it	Oracle creates it
Programmer controls lifecycle	Oracle manages lifecycle
Uses cursor_name%...	Uses SQL%...
Suitable for controlled multi-row processing	Convenient for single SQL statements

37. Explicit Cursor vs SELECT INTO

SELECT INTO: Suitable for single-row retrieval.

Explicit Cursor: Suitable for multiple rows and row-by-row processing.

38. Complete Example with All Four Steps

DECLARE
    CURSOR emp_cursor IS
        SELECT employee_id, first_name, salary
        FROM employees
        WHERE department_id = 10;


v_emp emp_cursor%ROWTYPE;


BEGIN
OPEN emp_cursor;


LOOP
    FETCH emp_cursor INTO v_emp;
    EXIT WHEN emp_cursor%NOTFOUND;

    DBMS_OUTPUT.PUT_LINE(
        'ID: ' || v_emp.employee_id ||
        ', Name: ' || v_emp.first_name ||
        ', Salary: ' || v_emp.salary
    );
END LOOP;

CLOSE emp_cursor;


END;
/

39. Frequently Asked Interview Questions

Question	Answer
What is an explicit cursor?	A cursor explicitly declared and controlled by the programmer to process rows one by one.
What are the four steps?	DECLARE → OPEN → FETCH → CLOSE
Which command opens a cursor?	OPEN cursor_name;
Which command retrieves a row?	FETCH cursor_name INTO variables;
Which command closes a cursor?	CLOSE cursor_name;
What does %FOUND mean?	The last fetch successfully retrieved a row.
What does %NOTFOUND mean?	The last fetch did not retrieve a row.
What does %ROWCOUNT mean?	Number of rows fetched so far.
What does %ISOPEN mean?	Whether the explicit cursor is currently open.
What happens if you fetch from a closed cursor?	An INVALID_CURSOR error occurs.

Important Exam Points

Explicit cursor = programmer-controlled cursor.
Mainly used for multiple-row processing.
Four traditional steps: DECLARE → OPEN → FETCH → CLOSE.
OPEN activates the cursor.
FETCH retrieves the next row.
CLOSE closes the cursor.
%FOUND means the last fetch found a row.
%NOTFOUND means the last fetch found no row.
%ROWCOUNT means number of rows fetched.
%ISOPEN means cursor open status.
cursor_name%ROWTYPE can represent one cursor row.
Parameterized cursors accept input values.
Cursor FOR loops automatically manage open, fetch, and close.
FOR UPDATE is used when selected rows need to be locked for update/delete processing.
WHERE CURRENT OF operates on the current row of a FOR UPDATE cursor.
An explicit cursor uses cursor_name%attribute, unlike an implicit cursor, which uses SQL%attribute.

One-Line Memory Trick

EXPLICIT CURSOR
      ↓
DECLARE
      ↓
OPEN
      ↓
FETCH
      ↓
%NOTFOUND?
   ↓       ↓
  NO      YES
  ↓        ↓
PROCESS   EXIT
           ↓
         CLOSE

Explicit Cursor = Programmer controls the cursor to process rows one by one.

Home

Oracle Explicit Cursor Faqs With Examples

1. What is an Explicit Cursor?

2. Why do we use Explicit Cursors?

3. What are the steps of an Explicit Cursor?

4. How do you declare an Explicit Cursor?

5. What is the OPEN operation?

6. What is FETCH?

7. What is CLOSE?

8. Complete Explicit Cursor Example

9. What are Explicit Cursor Attributes?

10. What is %FOUND?

11. What is %NOTFOUND?

12. What is %ROWCOUNT?

13. What is %ISOPEN?

14. Explicit Cursor Attributes — Summary

15. Why is %NOTFOUND important?

16. What is the correct position of %NOTFOUND?

17. Can an Explicit Cursor return multiple rows?

18. Can an Explicit Cursor return zero rows?

19. Explicit Cursor with WHERE Condition

20. What is a Parameterized Explicit Cursor?

21. Why use a Parameterized Cursor?

22. Can we create multiple Explicit Cursors?

23. What is a Cursor FOR LOOP?

24. Do we need OPEN, FETCH, and CLOSE with a Cursor FOR LOOP?

25. Explicit Cursor vs Cursor FOR LOOP

26. Can we use a query directly in a Cursor FOR LOOP?

27. What happens if you FETCH from a closed cursor?

28. What happens if you OPEN an already open cursor?

29. What happens if you CLOSE an already closed cursor?

30. What happens if the cursor query returns no rows?

31. Explicit Cursor with Record

32. What is %ROWTYPE with a Cursor?

33. Can an Explicit Cursor be used for UPDATE?

34. What is FOR UPDATE in an Explicit Cursor?

35. What is WHERE CURRENT OF?

36. Explicit Cursor vs Implicit Cursor

37. Explicit Cursor vs SELECT INTO

38. Complete Example with All Four Steps

39. Frequently Asked Interview Questions

Important Exam Points

One-Line Memory Trick

No comments:

Post a Comment