G-4120

Error

Avoid using %NOTFOUND directly after the FETCH when working with BULK OPERATIONS and LIMIT clause.

Control

Rule Details

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

Reason

%notfound is set to true as soon as less than the number of rows defined by the limit clause has been read.

Examples

Non-Compliant Example

declare
   cursor c_employees is
      select *
        from employees
       order by employee_id;

   type t_employees_type is table of c_employees%rowtype;
   t_employees  t_employees_type;
   co_bulk_size constant simple_integer := 10;
begin
   open c_employees;

   <<process_employees>>
   loop
      fetch c_employees bulk collect into t_employees limit co_bulk_size;
      exit process_employees when c_employees%notfound;

      <<display_employees>>
      for i in 1..t_employees.count()
      loop
         sys.dbms_output.put_line(t_employees(i).last_name);
      end loop display_employees;
   end loop process_employees;

   close c_employees;
end;
/

Issues

Line	Column	Message
16	35	Using %NOTFOUND with LIMIT clause.

The employees table holds 107 rows. The example below will only show 100 rows as the cursor attribute notfound is set to true as soon as the number of rows to be fetched defined by the limit clause is not fulfilled anymore.

Compliant Solution - ★★★☆☆

declare
   cursor c_employees is
      select *
        from employees
       order by employee_id;

   type t_employees_type is table of c_employees%rowtype;
   t_employees  t_employees_type;
   co_bulk_size constant simple_integer := 10;
begin
   open c_employees;

   <<process_employees>>
   loop
      fetch c_employees bulk collect into t_employees limit co_bulk_size;
      exit process_employees when t_employees.count() = 0;
      <<display_employees>>
      for i in 1..t_employees.count()
      loop
         sys.dbms_output.put_line(t_employees(i).last_name);
      end loop display_employees;
   end loop process_employees;

   close c_employees;
end;
/

This example will show all 107 rows but execute one fetch too much (12 instead of 11).

Compliant Solution - ★★★★★

declare
   cursor c_employees is
      select *
        from employees
       order by employee_id;

   type t_employees_type is table of c_employees%rowtype;
   t_employees  t_employees_type;
   co_bulk_size constant simple_integer := 10;
begin
   open c_employees;

   <<process_employees>>
   loop
      fetch c_employees bulk collect into t_employees limit co_bulk_size;
      <<display_employees>>
      for i in 1..t_employees.count()
      loop
         sys.dbms_output.put_line(t_employees(i).last_name);
      end loop display_employees;
      exit process_employees when t_employees.count() <> co_bulk_size;
   end loop process_employees;

   close c_employees;
end;
/

This example does the trick (11 fetches only to process all rows)

Parameters

Use parameters to customize the rule to your needs.

Parameter	Description	Default Value
DisableAllQuickFix	Comma-separated list of rules for which a quick fix should not be applied to all the problems in a file.	Core G-3130

References

same as Trivadis G-4120