Oracle Cursor Attributes FAQs with Examples

1. What are Cursor Attributes?

Cursor attributes are properties that tell you what happened during cursor processing.

For an implicit cursor:

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

For an explicit cursor:

emp_cursor%FOUND
emp_cursor%NOTFOUND
emp_cursor%ROWCOUNT
emp_cursor%ISOPEN

2. What are the four main Cursor Attributes?

Attribute Meaning
%FOUND Indicates whether a row was found or affected
%NOTFOUND Indicates whether no row was found or affected
%ROWCOUNT Number of rows affected or fetched
%ISOPEN Indicates whether an explicit cursor is open

Memory trick: F-N-R-I = Found, Not Found, Row Count, Is Open

3. What is %FOUND?

%FOUND tells whether the most recent cursor operation successfully found or affected a row, as applicable.

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

If the fetch retrieves a row, the output is:

Row found

4. What is SQL%FOUND?

For an implicit cursor, use SQL%FOUND.

Example:

BEGIN
    UPDATE employees
    SET salary = salary + 1000
    WHERE employee_id = 101;


IF SQL%FOUND THEN
    DBMS_OUTPUT.PUT_LINE('Employee updated');
END IF;


END;
/

If employee 101 was updated, %FOUND is TRUE.

5. What is %NOTFOUND?

%NOTFOUND indicates that the most recent cursor operation did not find or affect a row, as applicable. It is particularly important when processing an 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_name);
END LOOP;

CLOSE emp_cursor;


END;
/

Flow:

FETCH
  ↓
Row found?
  ↓
YES → Process row
  ↓
FETCH again
  ↓
No row
  ↓
%NOTFOUND = TRUE
  ↓
EXIT

6. What is SQL%NOTFOUND?

For an implicit cursor, SQL%NOTFOUND can be used after DML to determine that no rows were affected.

Example:

BEGIN
    DELETE FROM employees
    WHERE employee_id = 9999;


IF SQL%NOTFOUND THEN
    DBMS_OUTPUT.PUT_LINE('No employee deleted');
END IF;


END;
/

If employee 9999 does not exist, the output is:

No employee deleted

7. What is %ROWCOUNT?

%ROWCOUNT tells the number of rows processed. For an explicit cursor, it gives the number of rows fetched so far.

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(
        'Rows fetched: ' || emp_cursor%ROWCOUNT
    );
END LOOP;

CLOSE emp_cursor;


END;
/

The values increase as rows are fetched:

Rows fetched: 1
Rows fetched: 2
Rows fetched: 3
...

8. What is SQL%ROWCOUNT?

For an implicit cursor, SQL%ROWCOUNT tells the number of rows affected by the most recent DML statement.

Example:

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


DBMS_OUTPUT.PUT_LINE(
    'Rows updated: ' || SQL%ROWCOUNT
);


END;
/

If 10 employees are updated, the output is:

Rows updated: 10

9. What is %ISOPEN?

%ISOPEN tells whether an explicit cursor is currently open.

Example:

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


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

CLOSE emp_cursor;


END;
/

Output:

Cursor is open

10. What is SQL%ISOPEN?

For an implicit cursor, Oracle manages the cursor automatically.

Example:

BEGIN
    UPDATE employees
    SET salary = salary + 1000
    WHERE employee_id = 101;


IF SQL%ISOPEN THEN
    DBMS_OUTPUT.PUT_LINE('Open');
ELSE
    DBMS_OUTPUT.PUT_LINE('Closed');
END IF;


END;
/

After the statement completes, the implicit cursor is closed, so the result is:

Closed

Exam point: SQL%ISOPEN is always FALSE after the implicit SQL statement has completed.

11. Difference between implicit and explicit cursor attributes

Implicit Cursor Explicit Cursor
SQL%FOUND emp_cursor%FOUND
SQL%NOTFOUND emp_cursor%NOTFOUND
SQL%ROWCOUNT emp_cursor%ROWCOUNT
SQL%ISOPEN emp_cursor%ISOPEN

12. Example of all four explicit cursor attributes

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;
IF emp_cursor%ISOPEN THEN
    DBMS_OUTPUT.PUT_LINE('Cursor is open');
END IF;
LOOP
    FETCH emp_cursor INTO v_id, v_name;

    IF emp_cursor%FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Employee: ' || v_name);
        DBMS_OUTPUT.PUT_LINE('Rows fetched: ' || emp_cursor%ROWCOUNT);
    END IF;

    EXIT WHEN emp_cursor%NOTFOUND;
END LOOP;
CLOSE emp_cursor;
IF NOT emp_cursor%ISOPEN THEN
    DBMS_OUTPUT.PUT_LINE('Cursor is closed');
END IF;
END;
/

This demonstrates %ISOPEN, %FOUND, %ROWCOUNT, and %NOTFOUND.

13. Why is %NOTFOUND usually checked after FETCH?

Because the cursor needs to attempt a fetch before it can determine whether another row exists.

LOOP
    FETCH emp_cursor INTO v_id, v_name;
    EXIT WHEN emp_cursor%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(v_name);
END LOOP;

14. What happens to %ROWCOUNT after each FETCH?

For an explicit cursor:

First successful FETCH  → %ROWCOUNT = 1
Second successful FETCH → %ROWCOUNT = 2
Third successful FETCH  → %ROWCOUNT = 3

15. Example of %ROWCOUNT with INSERT

BEGIN
    INSERT INTO employees
    (employee_id, first_name, salary)
    VALUES
    (101, 'Ravi', 50000);
DBMS_OUTPUT.PUT_LINE(
    'Rows inserted: ' || SQL%ROWCOUNT
);
END;
/

Output:

Rows inserted: 1

16. Example of %ROWCOUNT with DELETE

BEGIN
    DELETE FROM employees
    WHERE department_id = 20;
DBMS_OUTPUT.PUT_LINE(
    'Rows deleted: ' || SQL%ROWCOUNT
);


END;
/

If 7 rows were deleted, the output is:

Rows deleted: 7

17. Example of %ROWCOUNT with UPDATE

BEGIN
    UPDATE employees
    SET salary = 60000
    WHERE department_id = 10;


IF SQL%ROWCOUNT > 0 THEN
    DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' employees updated');
ELSE
    DBMS_OUTPUT.PUT_LINE('No employees updated');
END IF;


END;
/

18. Can %ROWCOUNT be zero?

Yes.

BEGIN
    DELETE FROM employees
    WHERE employee_id = 9999;

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

If the employee does not exist, the output is:

0

19. Can %FOUND and %NOTFOUND both be TRUE?

No. For a given cursor state, they represent opposite conditions.

Conceptually:

%FOUND    = TRUE  → row found
%NOTFOUND = TRUE  → row not found

20. What happens to cursor attributes after another SQL statement?

Cursor attributes can be affected by subsequent SQL operations.

Best practice: save the value if you need it later.

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


v_count := SQL%ROWCOUNT;

DBMS_OUTPUT.PUT_LINE('Employees updated: ' || v_count);


END;
/

21. What happens if %NOTFOUND is used before the first FETCH?

For an explicit cursor, you should not use %NOTFOUND as the loop condition before the first fetch. Use the standard fetch-then-exit pattern.

22. What happens if you use %FOUND after a failed FETCH?

After a fetch that does not retrieve a row:

%FOUND = FALSE
%NOTFOUND = TRUE

23. Cursor Attributes with Cursor FOR LOOP

In a Cursor FOR LOOP, you generally do not need to manually check %FOUND or %NOTFOUND.

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

24. Explicit Cursor Attribute Example with %ROWTYPE

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('ID: ' || v_emp.employee_id);
    DBMS_OUTPUT.PUT_LINE('Name: ' || v_emp.first_name);
    DBMS_OUTPUT.PUT_LINE('Rows fetched: ' || emp_cursor%ROWCOUNT);
END LOOP;

CLOSE emp_cursor;


END;
/

25. Frequently Asked Questions

Question Answer
What are cursor attributes? Properties that provide information about cursor processing.
What are the four main attributes? %FOUND, %NOTFOUND, %ROWCOUNT, %ISOPEN
Which attribute tells whether a row was found? %FOUND
Which attribute tells whether no row was found? %NOTFOUND
Which attribute gives the number of rows processed? %ROWCOUNT
Which attribute tells whether a cursor is open? %ISOPEN
What is the syntax for an implicit cursor attribute? SQL%ROWCOUNT
What is the syntax for an explicit cursor attribute? emp_cursor%ROWCOUNT
Which attribute is commonly used to exit an explicit cursor loop? %NOTFOUND
What does %ROWCOUNT mean for an explicit cursor? The number of rows fetched so far.
What does SQL%ROWCOUNT mean for an implicit cursor? The number of rows affected by the SQL statement.
What is SQL%ISOPEN after an implicit SQL statement completes? FALSE.

Important Exam Points

  1. Cursor attributes provide information about cursor processing.
  2. The four main attributes are %FOUND, %NOTFOUND, %ROWCOUNT, and %ISOPEN.
  3. Implicit cursor syntax uses SQL%attribute.
  4. Explicit cursor syntax uses cursor_name%attribute.
  5. %FOUND means row found or affected.
  6. %NOTFOUND means no row found or affected.
  7. %ROWCOUNT means number of rows processed.
  8. For explicit cursors, %ROWCOUNT increases after successful fetches.
  9. %ISOPEN indicates cursor open status.
  10. SQL%ISOPEN is FALSE after the implicit statement has completed.
  11. %NOTFOUND is commonly used to exit explicit cursor loops.
  12. Cursor attributes relate to the most recent relevant cursor operation.

Quick Revision Table

Attribute Explicit Cursor Implicit Cursor
Found emp_cursor%FOUND SQL%FOUND
Not found emp_cursor%NOTFOUND SQL%NOTFOUND
Row count emp_cursor%ROWCOUNT SQL%ROWCOUNT
Is open emp_cursor%ISOPEN SQL%ISOPEN

One-line memory trick: FOUND = Did it find? NOTFOUND = Did it fail to find? ROWCOUNT = How many? ISOPEN = Is the cursor open?

Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)