Skip to Main Content
Oracle PL/SQL for DBAs
book

Oracle PL/SQL for DBAs

by Arup Nanda, Steven Feuerstein
October 2005
Intermediate to advanced content levelIntermediate to advanced
454 pages
14h 44m
English
O'Reilly Media, Inc.
Content preview from 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 ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

Oracle PL/SQL Best Practices

Oracle PL/SQL Best Practices

Steven Feuerstein
Expert Oracle PL/SQL

Expert Oracle PL/SQL

Ron Hardman, Michael McLaughlin
Oracle PL/SQL For Dummies

Oracle PL/SQL For Dummies

Michael Rosenblum, Paul Dorsey

Publisher Resources

ISBN: 0596005873Supplemental ContentErrata Page