G-4387: Never use a FOR LOOP for a query that should return not more than one row.

Blocker

Reliability, Efficiency, Maintainability

Unsupported in db* CODECOP Validators

Without access to the Oracle Data Dictionary, we cannot determine the number of rows to be processed.

Reason

A for loop can hide a too_many_rows exception. The more complex a query is, the higher is the risk that more than one row will be processed. This affects performance and can lead to a wrong result.

A for loop can also hide a no_data_found exception and the reader cannot determine whether this is intentional or not.

Example (bad)

create or replace package body employee_api is
   function emp_name(in_empno in integer) return varchar2 is -- NOSONAR: non-deterministic
      l_ename emp.ename%type;
   begin
      <<fetch_name>>
      for r in (
         select ename
           from emp
          where empno = in_empno
      )
      loop
         l_ename := r.ename;
      end loop fetch_name;
      return l_ename;
   end emp_name;
end employee_api;
/

Example (good)

create or replace package body employee_api is
   function emp_name(in_empno in integer) return varchar2 is -- NOSONAR: non-deterministic
      l_ename emp.ename%type;
   begin
      select ename
        into l_ename
        from emp
       where empno = in_empno;
      return l_ename;
   exception
      when no_data_found then
         return null;
      when too_many_rows then
         raise;
   end emp_name;
end employee_api;
/