11.3 How do I…Use cursor attributes to monitor cursor state?

Problem

When I query records from tables and views using cursors, I need to know whether the query returned any rows or not and the number of rows fetched by the cursor. I also need to know whether the cursor is already open before opening a cursor. In some cases, if I issue an UPDATE statement on a single record, I want to know if there is no matching record in the table so that I can insert a record in the table. How do I use cursor attributes to monitor cursor state?

Technique

Cursors have four attributes: %FOUND, %ISOPEN, %NOTFOUND, and %ROWCOUNT. Table 11.1 explains the meaning of each attribute. A cursor attribute appended to the cursor name acts like a built-in variable that can be tested to find out information about the execution of the cursor. How-To’s 11.1 and 11.2 demonstrate the use of explicit cursors to execute a multi-row query and how an implicit cursor is used by Oracle in executing a SELECT…INTO, INSERT, UPDATE, or DELETE statement. Cursor attributes can be used with both implicit and explicit cursors, but differences exist in terms of which attributes can be meaningfully used with implicit cursors.

Table 11.1 Cursor attributes

Attribute Meaning
%FOUND Boolean attribute that returns TRUE if the record returned by the last fetch attempt is successful
%NOTFOUND Boolean attribute that always returns the opposite of %FOUND
%ISOPEN Boolean attribute that returns TRUE if the cursor is open
%ROWCOUNT Numeric attribute that returns the number of records fetched from the cursor.

With explicit cursors, more than one row can be returned by the query that forms the result set. As rows are fetched one at a time from the result set, the number of rows currently fetched is returned by the %ROWCOUNT attribute. The %FOUND attribute is TRUE if the previous fetch returned a row and is FALSE otherwise. Vice versa, the %NOTFOUND attribute is TRUE if the previous fetch did not return a row and FALSE otherwise. The %ISOPEN attribute yields TRUE if the cursor is open, FALSE otherwise.

With an implicit cursor, the query must return exactly one row, and the values of the cursor attributes always refer to the most recently executed SQL statement. Oracle uses a cursor named SQL as the implicit cursor, and the attributes of the SQL cursor are set to NULL whenever an implicit cursor is opened. The implicit cursor is automatically closed after execution of the SQL statement, so SQL%ISOPEN always returns FALSE.

If a SELECT…INTO statement returns more than one row, the TOO_MANY_ROWS exception is raised; and if no rows are returned, the NO_DATA_FOUND exception is raised. Naturally, the SQL%ROWCOUNT has to be 1 for the SELECT…INTO statement to execute successfully. Similarly, the SQL%FOUND and SQL%NOTFOUND attributes have no meaningful use with a SELECT…INTO statement. If the TOO_MANY_ROWS exception is raised, the %ROWCOUNT is still 1 and not the actual number of rows returned by the query. Neither the NO_DATA_FOUND exception is raised, nor the SQL%NOTFOUND set to TRUE, however, if the SELECT…INTO statement uses SQL group functions, such as AVG or SUM, which always return a value or NULL.

Steps

1. If you have not already created the DEPT11 table and populated it with sample data in How-To 11.1, then run SQL*Plus, connect as the WAITE user account, and run CHP11_1.SQL.

2. Run the CHP11_4.SQL file in SQL*Plus (see Figure 11.4). The PL/SQL block contained in the file demonstrates the use of %FOUND and %ROWCOUNT attributes of an implicit cursor.

Until a DML statement is executed, the SQL%FOUND and SQL%ROWCOUNT attributes of the implicit SQL cursor are NULL. Line 2 deletes two records from the DEPT11 table. After the DELETE statement successfully deletes rows from the DEPT11 table, the SQL%FOUND attribute is set to TRUE. The SQL%ROWCOUNT attribute is set to the number of rows deleted. The IF statement in line 4 checks the value of the SQL%FOUND attribute, and if TRUE, the message in line 5 displays the number of rows deleted.

Because two records are deleted from the DEPT11 table, the SQL%FOUND attribute evaluates to TRUE, and the number of rows deleted is displayed in SQL*Plus.

3. Run the CHP11_5.SQL file in SQL*Plus (see Figure 11.5). The PL/SQL block contained in the file demonstrates the use of the SQL%NOTFOUND attribute of an implicit cursor after an UPDATE statement fails to match any rows.

The SQL%NOTFOUND attribute of the implicit SQL cursor is NULL at the beginning. Lines 2 through 4 attempt to update a record in the DEPT11 table. The record is non-existent because it was deleted from the table in Step 2. Because no rows are affected by the UPDATE statement, the SQL%NOTFOUND attribute is set to TRUE. Lines 5 to 8 ensure that, if the UPDATE statement failed because of a non-existent record, then that record gets inserted into the table. Run a simple query on the DEPT11 table to check whether the record is actually inserted.

SQL> SELECT * FROM DEPT11;

DEPT_NO DEPT_NAME

--------- ------------------------------

1 Marketing

2 Sales

3 Tech Support

Because no records are to be updated in the DEPT11 table, the SQL%NOTFOUND attribute evaluates to TRUE and the record is inserted into the DEPT11 table, as seen from the query results.

4. The %FOUND attribute of an explicit cursor is explained in How-To 11.2. For an example of using the %FOUND attribute to loop through records of an explicit cursor, please see How-To 11.2.

5. The PL/SQL block contained in the CHP11_6.SQL file, shown in Figure 11.6, demonstrates the use of the %NOTFOUND and %ISOPEN attributes of an explicit cursor.

Lines 2 and 3 contain the declarative section of the block. An explicit cursor is defined in line 2, and line 3 uses the %ROWTYPE attribute to declare a record variable representing all the columns returned by the cursor. Lines 5 through 12 contain the executable section of the block. Line 5 opens the cursor, and the loop in lines 6 through 11 fetch the rows from the cursor. In line 7, after a FETCH is issued to retrieve the next row from the cursor, the %NOTFOUND attribute is set to FALSE if the FETCH was successful, TRUE otherwise. For each row that is fetched, the column values are displayed in lines 9 and 10. If the FETCH failed to retrieve a row from the cursor, the %NOTFOUND attribute is set to TRUE and the loop is immediately exited in line 8 as the condition in the EXIT WHEN statement is satisfied.

Line 12 closes the cursor to release system resources used by the cursor. Lines 13 through 17 contain the exception section of the block. If an error occurred while processing, the %ISOPEN attribute is checked to see whether the cursor is open, to close the cursor before exiting the block. It is a good programming practice to include code to close any open cursors in the execution section as well as in the exception section, just in case an error occurred during execution. When a cursor is closed, system resources taken up by the cursor are released.

The three records present in the DEPT11 table are fetched in a loop and the column values are displayed, but when the %NOTFOUND attribute evaluates to TRUE, the loop is exited.

6. Run the CHP11_7.SQL file in SQL*Plus (see Figure 11.7). The PL/SQL block contained in the file explores the use of the %ROWCOUNT attribute of an explicit cursor and demonstrates the use of the UPDATE…WHERE CURRENT OF statement while using cursors.

Lines 2 to 4 contain the declarative section of the block. An explicit cursor is defined in line 2 and 3. Note the use of the FOR UPDATE OF clause of the SELECT statement in line 3 while declaring a cursor for update operations. Line 4 uses the %TYPE attribute to declare a variable representing the DEPT_NAME column of the DEPT11 table. Line 6 opens the cursor, and the loop in lines 7 through 14 fetch the rows from the cursor. In line 8, after a FETCH is issued to retrieve the next row from the cursor, the %NOTFOUND attribute is set to FALSE if the FETCH was successful and TRUE otherwise. The %ROWCOUNT is set to 0 after the cursor has been opened, but no fetch has been done. For each row fetched, the %ROWCOUNT is incremented by 1.

When the %ROWCOUNT reaches 3, as checked for in line 10, an UPDATE statement is issued in lines 11 and 12 using the WHERE CURRENT OF clause, to update the row that was fetched most recently. It is not necessary to use the %ROWCOUNT when using the WHERE CURRENT OF clause. This is just an example where two techniques are shown in a single step. If the FETCH fails to retrieve a row from the cursor, the %NOTFOUND attribute is set to TRUE, and the loop is immediately exited in line 9 as the condition in the EXIT WHEN statement is satisfied. Line 15 closes the cursor to release system resources used by the cursor. You can query the DEPT11 table to see whether the intended row is updated.

How It Works

INSERT, UPDATE, DELETE, and SELECT…INTO statements are executed by Oracle using the SQL cursor. Cursor attributes can be applied to the SQL cursor. The SQL%ISOPEN always returns FALSE. Step 1 creates the sample table and data, and Step 2 shows the use of SQL%ROWCOUNT and SQL%FOUND after executing a DELETE statement. The SQL%NOTFOUND attribute is checked in Step 3 after executing an UPDATE statement, and if set to TRUE, a record is inserted into the DEPT11 table. With a SELECT…INTO statement, if more than one record is returned by the statement, the TOO_MANY_ROWS exception is raised; if no records are returned, the NO_DATA_FOUND exception is raised. Hence, the SQL cursor attributes have no meaningful use when using a SELECT…INTO statement. Step 4 makes use of the %FOUND attribute of an explicit cursor to check whether a record is returned after executing the FETCH statement. Step 5 uses %NOTFOUND, which is the exact opposite of %FOUND and is used to exit the loop if no more remaining rows are to be fetched from the cursor. Step 6 demonstrates the use of the %ROWCOUNT attribute and exemplifies the declaration of an update cursor and the use of the UPDATE…WHERE CURRENT OF statement.

Comments

You can use cursor attributes only in procedural statements, not in SQL statements. Cursor attributes are set to NULL before the implicit SQL cursor is opened for executing a SQL statement. Cursor attribute values refer to the most recently executed SQL statements.

Note that after you execute a SQL statement, call a stored procedure, and then check the values of the SQL cursor attributes, they might not be what you expect. This is because if the stored procedure executed any SQL statements, the values of the SQL cursor attributes refer to the results of the execution of the SQL statement in the stored procedure. As a workaround, you can save values of the relevant cursor attributes in PL/SQL variables before calling the stored procedure. The INVALID_CURSOR predefined exception is raised if you try to use cursor attributes with an explicit cursor that has not been opened or if the cursor has been closed. Cursor attributes can also be used with cursor variables, which is the topic of discussion of How-To 11.5.