G-8120

🆓

Warning

Never check existence of a row to decide whether to create it or not.

Rule Details

Severity Level: Critical
Quality Area: Reliability
Fixing times: 10 mins
Local-high
Database Support: OracleDB (v7.3.4 +)

Reason

The result of an existence check is a snapshot of the current situation. You never know whether in the time between the check and the (insert) action someone else has decided to create a row with the values you checked. Therefore, you should only rely on constraints when it comes to prevention of duplicate records.

Example

Non-Compliant Example

create or replace package body department_api is
   procedure ins(in_r_department in departments%rowtype) is
      l_count pls_integer;
   begin
      select count(*)
        into l_count
        from departments
       where department_id = in_r_department.department_id;

      if l_count = 0 then
         insert into departments
         values in_r_department;
      end if;
   end ins;
end department_api;
/

Issues

Line	Column	Message
10	10	Checking existence of a row before insert.

Compliant Solution - ★★★★★

create or replace package body department_api is
   procedure ins(in_r_department in departments%rowtype) is
   begin
      insert into departments
      values in_r_department;
   exception
      when dup_val_on_index then
         null; -- handle exception
   end ins;
end department_api;
/

References

same as Trivadis G-8120