Oracle SQL Experts: Oracle Cursor Faqs With Examples

1. What is a Cursor in Oracle?

A cursor is a mechanism used by Oracle to process the result of a SQL statement, especially when working with rows returned by a query.

SQL Query
   ↓
Result Set
   ↓
Cursor
   ↓
Process rows

2. Why do we use Cursors?

Cursors are useful when you need to process query results row by row, perform calculations for each row, apply different logic to different rows, retrieve multiple rows in PL/SQL, or process records sequentially.

3. What are the types of Cursors?

Oracle cursors are mainly divided into two types: Implicit Cursor and Explicit Cursor.

             CURSORS
                |
        ┌───────┴───────┐
        ↓               ↓
    Implicit          Explicit
    Cursor            Cursor

4. What is an Implicit Cursor?

An implicit cursor is automatically created by Oracle whenever a SQL statement is executed. You do not need to declare, open, fetch from, or close it.

BEGIN
    UPDATE employees
    SET salary = salary + 1000
    WHERE department_id = 10;

    DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT);
END;
/

5. What is an Explicit Cursor?

An explicit cursor is a cursor that the programmer explicitly declares and manages. It is useful when a query returns multiple rows and you want to process them individually.

DECLARE
    CURSOR emp_cursor IS
        SELECT employee_id, first_name
        FROM employees;
BEGIN
    OPEN emp_cursor;

    -- Fetch/process rows here

    CLOSE emp_cursor;
END;
/

6. What are the steps of an Explicit Cursor?

The traditional explicit cursor lifecycle has four steps: DECLARE, OPEN, FETCH, and CLOSE.

DOFC = Declare → Open → Fetch → Close

7. How do you Declare a Cursor?

Syntax:

CURSOR cursor_name IS
    SELECT statement;

Example:

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

8. How do you Open a Cursor?

Use OPEN cursor_name; to execute the cursor query and make the result set available for fetching.

9. What is FETCH?

FETCH retrieves rows from the cursor's result set. Each fetch retrieves the next row into the specified variables.

FETCH emp_cursor
INTO v_id, v_name;

10. How do you Close a Cursor?

Use CLOSE cursor_name; to release the cursor resources after processing is complete.

11. Complete Explicit Cursor 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;
/

DECLARE cursor
      ↓
OPEN cursor
      ↓
FETCH first row
      ↓
Process row
      ↓
FETCH next row
      ↓
Process row
      ↓
...
      ↓
%NOTFOUND = TRUE
      ↓
EXIT LOOP
      ↓
CLOSE cursor

12. What are Cursor Attributes?

Oracle provides cursor attributes to get information about cursor execution.

For implicit cursors:

SQL%FOUND
SQL%NOTFOUND
SQL%ROWCOUNT
SQL%ISOPEN

For explicit cursors:

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

13. What is %FOUND?

%FOUND tells whether the most recent fetch or SQL operation affected or found a row.

14. What is %NOTFOUND?

%NOTFOUND becomes TRUE when the most recent fetch did not retrieve a row. It is commonly used to terminate 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;

15. What is %ROWCOUNT?

%ROWCOUNT tells how many rows have been fetched so far for an explicit cursor.

16. What is %ISOPEN?

%ISOPEN tells whether an explicit cursor is currently open.

17. Explicit Cursor Attributes — Summary

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

18. What is an Implicit Cursor Attribute?

For implicit SQL statements, Oracle uses the SQL cursor. The attributes are SQL%FOUND, SQL%NOTFOUND, SQL%ROWCOUNT, and SQL%ISOPEN.

19. Difference Between Implicit and Explicit Cursor

Implicit Cursor	Explicit Cursor
Created automatically	Declared by programmer
Oracle manages it	Programmer manages it
No OPEN required	Usually explicitly opened
No explicit FETCH required	Explicit FETCH is possible
No explicit CLOSE required	Programmer can explicitly close it
Uses SQL%...	Uses cursor_name%...

20. What is a Cursor FOR LOOP?

A cursor FOR loop simplifies explicit cursor processing. Oracle automatically handles opening the cursor, fetching rows, and closing the cursor.

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

21. Why is Cursor FOR LOOP useful?

It makes code shorter and reduces mistakes. Instead of manually writing OPEN, FETCH, LOOP, EXIT, and CLOSE, you can use a Cursor FOR loop.

22. Cursor FOR LOOP Without Declaring a 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;
/

23. What is a Parameterized Cursor?

A parameterized cursor accepts parameters when it is opened.

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

24. Why use Parameterized Cursors?

They allow the same cursor definition to be reused with different values.

25. What is a Nested Cursor?

A cursor can be used inside another loop or cursor-processing block. This is useful for department-by-department processing of employees.

BEGIN
    FOR dept IN (
        SELECT department_id
        FROM departments
    )
    LOOP
        FOR emp IN (
            SELECT employee_id, first_name
            FROM employees
            WHERE department_id = dept.department_id
        )
        LOOP
            DBMS_OUTPUT.PUT_LINE(emp.first_name);
        END LOOP;
    END LOOP;
END;
/

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

Oracle raises a cursor-related error such as INVALID_CURSOR.

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

Opening an already open explicit cursor causes an error such as INVALID_CURSOR.

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

Attempting to close a cursor that is not open causes an INVALID_CURSOR error.

29. Can a cursor return multiple rows?

Yes. Explicit cursors are commonly used to process multiple rows sequentially.

30. Can SELECT INTO use multiple rows?

A normal SELECT INTO expects a single row. If multiple rows are returned, Oracle raises TOO_MANY_ROWS.

31. Cursor and SELECT INTO Difference

SELECT INTO	Cursor
Generally expects one row	Can process multiple rows
Simple retrieval	Useful for row-by-row processing
Multiple rows can cause TOO_MANY_ROWS	Designed for multiple-row processing

32. What is a REF CURSOR?

A REF CURSOR is a cursor variable that can refer to different query result sets. It is commonly used when passing query results between PL/SQL programs and applications.

33. Strong REF CURSOR vs Weak REF CURSOR

A strong REF CURSOR has a specified return type. A weak REF CURSOR does not specify a fixed return type.

34. Implicit Cursor vs Cursor FOR LOOP

Do not confuse these concepts. An implicit cursor is automatically managed by Oracle for a SQL statement, while a Cursor FOR loop automatically manages the open, fetch, and close process for row-by-row iteration.

35. Complete Cursor Example for Exam

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

DECLARE
   ↓
Define cursor
   ↓
OPEN
   ↓
FETCH
   ↓
Check %NOTFOUND
   ↓
Process row
   ↓
FETCH next row
   ↓
CLOSE

36. Frequently Asked Interview Questions

Question	Answer
What is a cursor?	A mechanism for processing SQL query results, especially row by row.
What are the two main types of cursors?	Implicit and Explicit cursors.
Who manages an implicit cursor?	Oracle automatically manages it.
Who manages an explicit cursor?	The programmer controls its lifecycle.
What are the four steps of an explicit cursor?	DECLARE → OPEN → FETCH → CLOSE
Which attribute checks whether a row was found?	cursor_name%FOUND
Which attribute checks whether a row was not found?	cursor_name%NOTFOUND
Which attribute gives the number of rows fetched?	cursor_name%ROWCOUNT
Which attribute checks whether the cursor is open?	cursor_name%ISOPEN

Important Exam Points

A cursor is used to process SQL query results.
There are two main types: Implicit and Explicit.
Implicit cursors are automatically managed by Oracle.
Explicit cursors are declared by the programmer.
Traditional explicit cursor steps are DECLARE → OPEN → FETCH → CLOSE.
%FOUND tells whether the last fetch found a row.
%NOTFOUND tells whether the last fetch did not find a row.
%ROWCOUNT tells the number of rows fetched so far.
%ISOPEN tells whether an explicit cursor is open.
Cursor FOR loops automatically manage opening, fetching, and closing.
Parameterized cursors accept values when used or opened.
SELECT INTO is generally for a single-row result; cursors are suitable for multiple-row processing.
REF CURSOR is a cursor variable used to return or reference query result sets.

Easy Memory Trick

IMPLICIT
→ Oracle manages it

EXPLICIT
→ Programmer defines it

EXPLICIT CURSOR
→ DECLARE
→ OPEN
→ FETCH
→ CLOSE

ATTRIBUTES
→ FOUND
→ NOTFOUND
→ ROWCOUNT
→ ISOPEN

Home

Oracle Cursor Faqs With Examples