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.
|
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 ...