G-4140

Error

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

Rule Details

Severity Level: Blocker
Quality Area: Reliability
Fixing times: 5 mins
Local-medium
Database Support: OracleDB (v7.3.4 +)

Reason

The Oracle Database 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

Non-Compliant Example

create or replace package body employee_api as
   co_one constant simple_integer := 1;
   co_msg constant types_up.text  := 'Do something based on ';

   procedure process_dept(in_dept_id in departments.department_id%type) is
      co_dept_id constant departments.department_id%type := in_dept_id;
   begin
      sys.dbms_output.put_line(co_msg || co_dept_id);
   end process_dept;

   procedure remove_employee(in_employee_id in employees.employee_id%type) is
      co_employee_id constant departments.department_id%type := in_employee_id;
      l_dept_id      employees.department_id%type;
   begin
      delete from employees
       where employee_id = co_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;
/

Issues

Line	Column	Message
21	10	Multiple statements executed before checking sql%rowtype.

Compliant Solution - ★★★★★

create or replace package body employee_api as
   co_one constant simple_integer := 1;
   co_msg constant types_up.text  := 'Do something based on ';

   procedure process_dept(in_dept_id in departments.department_id%type) is
      co_dept_id constant departments.department_id%type := in_dept_id;
   begin
      sys.dbms_output.put_line(co_msg || co_dept_id);
   end process_dept;

   procedure remove_employee(in_employee_id in employees.employee_id%type) is
      co_employee_id constant departments.department_id%type := in_employee_id;
      l_dept_id      employees.department_id%type;
      l_deleted_emps simple_integer;
   begin
      delete from employees
       where employee_id = co_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;
/

References

same as Trivadis G-4140