Oracle PL/SQL Programming Guide to Oracle 8i Features

Oracle PL/SQL Programming Guide to Oracle 8i FeaturesSearch this book
Previous: 5.3 Bulk Querying with the BULK COLLECT ClauseChapter 5
Bulking Up with PL/SQL 8.1
Next: 5.5 Analyzing the Impact of Bulk Operations
 

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.


Table 5.1: Cursor Attributes

Cursor Attribute

Effect

cur %FOUND

Returns TRUE if the last FETCH found a row

cur %NOTFOUND

Returns FALSE if the last FETCH found a row

cur %ISOPEN

Returns TRUE if the specified cursor is open

cur %ROWCOUNT

Returns the number of rows modified by the DML statement

SQL%BULK_ROWCOUNT

Returns the number of rows processed for each execution of the bulk DML operation

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:

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

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.


Previous: 5.3 Bulk Querying with the BULK COLLECT ClauseOracle PL/SQL Programming Guide to Oracle 8i FeaturesNext: 5.5 Analyzing the Impact of Bulk Operations
5.3 Bulk Querying with the BULK COLLECT ClauseBook Index5.5 Analyzing the Impact of Bulk Operations

The Oracle Library Navigation

Copyright (c) 2000 O'Reilly & Associates. All rights reserved.

Library Home Oracle PL/SQL Programming, 2nd. Ed. Guide to Oracle 8i Features Oracle Built-in Packages Advanced PL/SQL Programming with Packages Oracle Web Applications Oracle PL/SQL Language Pocket Reference Oracle PL/SQL Built-ins Pocket Reference