5.4 Using Cursor Attributes

Whenever you work with explicit and implicit cursors (including cursor variables), PL/SQL provides a set of cursor attributes that return information about the cursor. PL/SQL 8.1 adds another, composite attribute, SQL%BULK_ROWCOUNT, for use with or after the FORALL statement. All of the current attributes are summarized in Table 5.1.

In these attributes, cur is the name of an explicit cursor, a cursor variable, or the string "SQL" for implicit cursors (UPDATE, DELETE, and INSERT statements, since none of the attributes can be applied to an implicit query). The %BULK_ROWCOUNT structure has the same semantics as an index-by table. The n th row in this pseudo index-by table stores the number of rows processed by the n th execution of the DML operation in the FORALL statement.

Let's examine the behavior of these cursor attributes in FORALL and BULK COLLECT statements by running the script found in the showattr.sql file on the disk. I start out by creating a utility function and general show_attributes procedure:

/* Filename on companion disk: showattr.sql */
CREATE OR REPLACE FUNCTION boolstg (bool IN BOOLEAN)
   RETURN VARCHAR2
IS
BEGIN
   IF bool THEN RETURN 'TRUE ';
   ELSIF NOT bool THEN RETURN 'FALSE';
   ELSE RETURN 'NULL ';
   END IF;
END;
/

CREATE OR REPLACE PROCEDURE show_attributes (
   depts IN number_varray)
IS
BEGIN
   FORALL indx IN depts.FIRST .. depts.LAST
      UPDATE emp
         SET sal = sal + depts(indx)
       WHERE deptno = depts(indx);

   DBMS_OUTPUT.PUT_LINE (
      'FOUND-' || boolstg(SQL%FOUND) || ' ' ||
      'NOTFOUND-' || boolstg(SQL%NOTFOUND) || ' ' ||
      'ISOPEN-' || boolstg(SQL%ISOPEN) || ' ' ||
      'ROWCOUNT-' || NVL (TO_CHAR (SQL%ROWCOUNT), 'NULL'));

   FOR indx IN depts.FIRST .. depts.LAST
   LOOP
      DBMS_OUTPUT.PUT_LINE (
         depts(indx) || '-' || SQL%BULK_ROWCOUNT(indx));
   END LOOP;

   ROLLBACK;
END;
/

Then I run a query to show some data and show the attributes for two different lists of department numbers, followed by a use of BULK COLLECT:

SELECT deptno, COUNT(*) FROM emp GROUP BY deptno;

DECLARE
   /* No employees in departments 98 and 99 */
   depts1 number_varray := number_varray (10, 20, 98);
   depts2 number_varray := number_varray (99, 98);
BEGIN
   show_attributes (depts1);
   show_attributes (depts2);
END;
/ 
DECLARE
   CURSOR allsals IS
      SELECT sal FROM emp;
   salaries number_varray;
BEGIN
   OPEN allsals; 
   FETCH allsals BULK COLLECT INTO salaries;
 
   DBMS_OUTPUT.PUT_LINE (
      'FOUND-' || boolstg(SQL%FOUND) || ' ' ||
      'NOTFOUND-' || boolstg(SQL%NOTFOUND) || ' ' ||
      'ISOPEN-' || boolstg(SQL%ISOPEN) || ' ' ||
      'ROWCOUNT-' || NVL (TO_CHAR (SQL%ROWCOUNT), 'NULL'));
END;
/ 

Here is the output from this script:

DEPTNO COUNT(*)
------ ---------
    10         3
    20         5
    30         6

FOUND-TRUE  NOTFOUND-FALSE ISOPEN-FALSE ROWCOUNT-8
10-3
98-0
20-5
FOUND-FALSE NOTFOUND-TRUE  ISOPEN-FALSE ROWCOUNT-0
99-0
98-0
FOUND-NULL  NOTFOUND-NULL  ISOPEN-FALSE ROWCOUNT-NULL

From this output, we can conclude the following:

• For FORALL, %FOUND and %NOTFOUND reflect the overall results, not the results of any individual statement, including the last (this contradicts Oracle documentation). In other words, if any one of the statements executed in the FORALL modified at least one row, %FOUND returns TRUE and %NOTFOUND returns FALSE.

• For FORALL, %ISOPEN always returns FALSE because the cursor is closed when the FORALL statement terminates.

• For FORALL, %ROWCOUNT returns the total number of rows affected by all the FORALL statements executed, not simply the last statement.

• For BULK COLLECT, %FOUND and %NOTFOUND always return NULL and %ISOPEN returns FALSE because the BULK COLLECT has completed the fetching and closed the cursor. %ROWCOUNT always returns NULL, since this attribute is only relevant for DML statements.

• The nth row in this pseudo index-by table stores the number of rows processed by the n th execution of the DML operation in the FORALL statement. If no rows are processed, then the value in %BULK_ROWCOUNT is set to 0.

The %BULK_ROWCOUNT attribute is a handy device, but it is also quite limited. Keep the following in mind:

• Even though it looks like an index-by table, you cannot apply any methods to it.

• %BULK_ROWCOUNT cannot be assigned to other collections. Also, it cannot be passed as a parameter to subprograms.

• The only rows defined for this pseudo index-by table are the same rows defined in the collection referenced in the FORALL statement.

• If you reference a row in %BULK_ROWCOUNT that is outside the defined subscripts, you will not raise a NO_DATA_FOUND error or subscript error. It will simply return a NULL value.

If I try to execute code like either of these statements:

DBMS_OUTPUT.PUT_LINE (SQL%BULK_ROWCOUNT.COUNT);

IF SQL%BULK_ROWCOUNT.FIRST IS NOT NULL

I get this error:

PLS-00332: "%BULK_ROWCOUNT" is not a valid prefix for a qualified name

All you can really do with %BULK_ROWCOUNT is reference individual rows in this special structure.