G-4140: Avoid executing any statements between a SQL operation and the usage of an implicit cursor attribute.

Major

Reliability

Reason

Oracle provides a variety of cursor attributes (like %FOUND and %ROWCOUNT) that can be used to obtain information about the status of a cursor, either implicit or explicit.

You should avoid inserting any statements between the cursor operation and the use of an attribute against that cursor. Interposing such a statement can affect the value returned by the attribute, thereby potentially corrupting the logic of your program.

In the following example, a procedure call is inserted between the DELETE statement and a check for the value of SQL%ROWCOUNT, which returns the number of rows modified by that last SQL statement executed in the session. If this procedure includes a COMMIT / ROLLBACK or another implicit cursor the value of SQL%ROWCOUNT is affected.

Example (bad)

CREATE OR REPLACE PACKAGE BODY employee_api AS
   co_one CONSTANT SIMPLE_INTEGER := 1;

   PROCEDURE process_dept(in_dept_id IN departments.department_id%TYPE) IS
   BEGIN
      NULL;
   END process_dept;

   PROCEDURE remove_employee (in_employee_id IN employees.employee_id%TYPE) IS
      l_dept_id      employees.department_id%TYPE;
   BEGIN
      DELETE FROM employees
       WHERE employee_id = in_employee_id
       RETURNING department_id INTO l_dept_id;

      process_dept(in_dept_id => l_dept_id);

      IF SQL%ROWCOUNT > co_one THEN
         -- too many rows deleted. 
         ROLLBACK;
      END IF;
   END remove_employee;
END employee_api;
/

Example (good)

CREATE OR REPLACE PACKAGE BODY employee_api AS
   co_one CONSTANT SIMPLE_INTEGER := 1;

   PROCEDURE process_dept(in_dept_id IN departments.department_id%TYPE) IS
   BEGIN
      NULL;
   END process_dept;

   PROCEDURE remove_employee (in_employee_id IN employees.employee_id%TYPE) IS
      l_dept_id      employees.department_id%TYPE;
      l_deleted_emps SIMPLE_INTEGER;
   BEGIN
      DELETE FROM employees
       WHERE employee_id = in_employee_id
       RETURNING department_id INTO l_dept_id;

      l_deleted_emps := SQL%ROWCOUNT;

      process_dept(in_dept_id => l_dept_id);

      IF l_deleted_emps > co_one THEN
         -- too many rows deleted. 
         ROLLBACK;
      END IF;
   END remove_employee;
END employee_api;
/