You are probably familiar with the implicit cursor attributes available within PL/SQL; ROWCOUNT, FOUND, NOTFOUND, etc. As a quick review:
- SQL%ROWCOUNT returns the number of rows modified by the prevous SQL statement
- SQL%FOUND returns TRUE if the previous SQL statement returned a result (SELECT) or if a DML statement modified any rows
- SQL%NOTFOUND is the opposite of SQL%FOUND and returns TRUE if the previous SQL statement did not return any result or modify any data
- SQL%ISOPEN always returns FALSE for the implicit cursor as the cursor is closed after the execution of the SQL statement
So, a a simple example, if I wanted to find out how many rows an UPDATE statement had modified I would code something like the following:
...
UPDATE emp
SET sal = sal * 1.05
WHERE deptno = 10;
dbms_output.put_line ('Number of employees updated: ' || TO_CHAR(SQL%ROWCOUNT));
...
I do not intend to dwell on the use of the use of the attributes but rather focus on their placement within the code. The following sums up my opinion:
...
SQL statement (SELECT, INSERT, UPDATE, DELETE, MERGE)
<<<--- NOTHING EVER, EVER, EVER GOES HERE!!!!!!! --->>>
SQL%attribute use
...
My reasoning is quite simple, the implicit cursor attributes relate to the last SQL statement executed. If any code appear between the SQL statement and the use of the implicit cursor attribute then there is a risk that the implicit cursor attribute no longer reflects the SQL statement that you believe it to. For example, consider this code snippet:
...
UPDATE emp
SET sal = sal * 1.05
WHERE deptno = l_deptno;
dbms_output.put_line ('Number of employees updated: ' || TO_CHAR(SQL%ROWCOUNT));
IF (SQL%ROWCOUNT = 0) THEN
RAISE_APPLICATION_ERROR (-20000,'ERROR: No employees updated!');
END IF;
...
So, in the above code, we’re doing a simple update to the EMP table (l_deptno would be a variable), writing out a trace message using DBMS_OUTPUT and then raising an exception if we find that no rows got updated. Quite simple and nothing obviously amiss.
Suppose however that a developer later comes along and decides the DBMS_OUTPUT is not a suitable facility for writing trace messages. The developer has created a routine LOG.MSG and, since the call interface to this routine is the same as the PUT_LINE routine, a search and replace on the code should work nicely. Hence the code ends up as:
...
UPDATE emp
SET sal = sal * 1.05
WHERE deptno = l_deptno;
log.msg ('Number of employees updated: ' || TO_CHAR(SQL%ROWCOUNT));
IF (SQL%ROWCOUNT = 0) THEN
RAISE_APPLICATION_ERROR (-20000,'ERROR: No employees updated!');
END IF;
...
The bug lies in the reference to SQL%ROWCOUNT in the IF block. We might think that the reference to the implicit cursor relates to the UPDATE statement but we’re now making a call to LOG.MSG between the SQL statement and the use of the SQL%ROWCOUNT. If within LOG.MSG, we execute something like:
INSERT INTO log_msg (...) VALUES (...)
then the SQL%ROWCOUNT in the IF bock will be based on the INSERT operation contained in LOG.MSG and not the UPDATE statement we think it refers to.
Initial examination of the code shows nothing obviously amiss and the code compiles. Furthermore, it’s likely that the code will pass a number of unit tests. It will only fail a test that deliberately updates no EMP rows and tries to ensure the correct exception is raised. Hopefully such a unit test exists…
If I were feeling particularly cautious, I would recommend the following:
SQL statement (SELECT, INSERT, UPDATE, DELETE, MERGE)
assign SQL%attriubte to local variable
Use local variable instead of SQL% attribute here
By placing the implicit cursor attributes required into local variables the risk of anything going wrong is further lessened. Defensive programming techniques like this help make code more robust over a longer period of time.
Okay, I’m sure you’re getting my point by now…
… but one further thing to note before I finish up. Let’s take a look at a slightly different scenario:
...
UPDATE emp
SET sal = sal * 1.05
WHERE deptno = 10;
COMMIT;
dbms_output.put_line ('Number of employees updated: ' || TO_CHAR(SQL%ROWCOUNT));
...
In this scenario I’ve added a COMMIT between the UPDATE statement and the DBMS_OUTPUT line. So, what does SQL%ROWCOUNT return?
The answer is 0. In the above scenario, the SQL%ROWCOUNT reflects the row count of the COMMIT statement, not the UPDATE statement. The implicit cursor attributes reflect the operation of the last SQL statement and isn’t restricted to simple DML statements. COMMIT and ROLLBACK will return 0 for the ROWCOUNT attribute. A “CREATE TABLE … AS SELECT…” operation, if executed using EXECUTE IMMEDIATE, will return the number of rows inserted into the newly created table.
So, in summary, I would urge developers to be careful of where they place references to the implicit cursor attributes. If any code comes between the SQL statement and the implicit cursor attribute reference then the attribute value should be captured into a local variable and the code should then reference the variable.
Mark Hoxey 