Oracle PL/SQL for DBAs

Cursor Attributes

PL/SQL offers a set of cursor attributes that can be used to obtain information about the cursor’s state. These attributes are shown in Table 1-5. Generally, if you try to reference one of these attributes for a cursor that has not yet been opened, Oracle will raise the INVALID_CURSOR exception.

Table 1-5. Cursor attributes

Name	Description
%FOUND	Returns TRUE if data was fetched, FALSE otherwise
%NOTFOUND	Returns TRUE if data was not fetched, FALSE otherwise
%ROWCOUNT	Returns number of rows fetched from cursor at that point in time
%ISOPEN	Returns TRUE if cursor is open, FALSE otherwise
%BULK_ROWCOUNT	Returns the number of rows modified by the FORALL statement for each collection element
%BULK_EXCEPTIONS	Returns exception information for rows modified by the FORALL statement for each collection element

To reference a cursor attribute, attach it to the name of the cursor or cursor variable about which you want information. Here are some examples:

• Is the explicit cursor still open?

  DECLARE
     CURSOR happiness_cur IS SELECT simple_delights FROM ...;
  BEGIN
     OPEN happiness_cur;
     ...
     IF happiness_cur%ISOPEN THEN ...

• How many rows did I retrieve with the implicit bulk query? (Notice that the “name” of my cursor in this case is the generic “SQL.”)

  DECLARE
     TYPE id_nt IS TABLE OF department.department_id;
     deptnums   id_nt;
  BEGIN
     SELECT department_id
       BULK COLLECT INTO deptnums
       FROM department;
  
     DBMS_OUTPUT.PUT_LINE (SQL%BULK_ROWCOUNT);
  END;

Warning

You can reference cursor ...