Cursor Attributes in Oracle

Cursor attributes in Oracle – Learn about Cursor attribute types related PL-SQL Implicit Cursor and also Learn how to use the attributes with Examples.

PL/SQL Cursor Attributes

Cursor attributes are related to implicit cursors , which gives the information about most recently executed DML commands.
There are four cursor attributes available in PL-SQL.
cursor attributes are appended to cursor name simalar to %TYPE and %ROWTYPE
These are the main attributes of a PL/SQL cursor and their descriptions.

Attribute	Description
cursor_name%FOUND	returns TRUE if record was fetched successfully by cursor cursor_name
cursor_name%NOTFOUND	returns TRUE if record was not fetched successfully by cursor cursor_name
cursor_name%ROWCOUNT	returns the number of records fetched from the cursor cursor_name at the time we test %ROWCOUNT attribute
cursor_name%ISOPEN	returns TRUE if the cursor cursor_name is open

%FOUND:

Boolean attribute that returns TRUE if most recent sql statement affects one or more rows.

Note
Before execution DML statement %FOUND give NULL value. Only after execution of DML statement ,it return either TRUE or FALSE based on the number of rows affected.

%NOTFOUND

Boolean attribute that returns TRUE if most recent sql statement does not affect any rows.

Note

Before execution DML statement %NOTFOUND gives NULL, Only after execution of DML statement, it return either TRUE or FALSE based on the number of rows affected.
It is pure opposite to %FOUND attribute.

%ISOPEN

Boolean attribute that returns TRUE if the cursor is open else returns FALSE.

%ROWCOUNT

returns an integer value, that Number of rows affected by the most recent SQL statement.

The SQL cursor has additional attributes, %BULK_ROWCOUNT and %BULK_EXCEPTIONS, designed for use with the FORALL statement.

Example Program using % found attribute.

In the below example, SQL%FOUND is evaluating after execution of Delete statement.

It return true if rows deleted else it return false.

CREATE TABLE dept_temp AS
SELECT * FROM Department_TBL1;

DECLARE
  DEPT_ID NUMBER := 4;
BEGIN
  DELETE FROM dept_temp WHERE ID = DEPT_ID;
  IF SQL%FOUND THEN -- delete succeeded
    DBMS_OUTPUT.PUT_LINE('ROW DELETE WITH ID = '||DEPT_ID);
  ELSE
    DBMS_OUTPUT.PUT_LINE('NO ROW DELETE WITH ID = '||DEPT_ID);
  END IF;
END;

Output

ROW DELETE WITH ID = 4

Example Program using %NOTFOUND attribute.

In the below example, SQL%NOTFOUND is evaluating after execution of Delete statement.

It return true, if no rows deleted else it return false.

CREATE TABLE dept_temp AS
SELECT * FROM Department_TBL1;

DECLARE
  DEPT_ID NUMBER := 4;
BEGIN
  DELETE FROM dept_temp WHERE ID = DEPT_ID;
  IF SQL%NOTFOUND THEN -- delete fails
    DBMS_OUTPUT.PUT_LINE('NO ROW DELETE WITH ID = '||DEPT_ID);
  ELSE
    DBMS_OUTPUT.PUT_LINE('ROW  DELETE WITH ID = '||DEPT_ID);
  END IF;
END;

Output

ROW DELETE WITH ID = 4

Example Program using %ISOPEN attribute.

In the below example, we will see evaluation of cursor open.

DECLARE
  CURSOR Cur_ndp
  IS
    SELECT name,
      dept_name
    FROM employee_tbl1 e
    INNER JOIN department_tbl1 d
    ON d.id = e.departmentid;
BEGIN
  IF Cur_ndp%ISOPEN = FALSE THEN -- cursor was not already open
    DBMS_OUTPUT.PUT_LINE('CURSOR WAS NOT OPENED. PLEASE OPEN THE CURSOR');
    OPEN Cur_ndp;
  ELSE
    DBMS_OUTPUT.PUT_LINE('CURSOR IS OPENED');
  END IF;
  CLOSE Cur_ndp;
END;

Output

CURSOR WAS NOT OPENED. PLEASE OPEN THE CURSOR

Example Program using %ROWCOUNT attribute.

CREATE TABLE dept_temp AS
SELECT * FROM Department_TBL1;

DECLARE
  DEPT_ID NUMBER := 4;
BEGIN
  DELETE FROM dept_temp WHERE ID = DEPT_ID;
  IF SQL%ROWCOUNT > 0 THEN -- delete succeeded
    DBMS_OUTPUT.PUT_LINE(' ROWS DELETE WITH ID = '||DEPT_ID);
  ELSE
    DBMS_OUTPUT.PUT_LINE(' NO ROWS  DELETE WITH ID = '||DEPT_ID);
  END IF;
END;

Output

ROWS DELETE WITH ID = 4

Cursor Attributes in Oracle