Skip to content

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

Blocker

Reliability

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 (bad)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
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;
/

Example (good)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
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;
/