G-4140: Avoid executing any statements between a SQL operation and the usage of an implicit cursor attribute.
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 | 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)
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;
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;
/
|