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

Critical

Reliability

Reason

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

Example (bad)

The employee 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.

 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
declare
   cursor employee_cur is 
      select *
        from employee
       order by employee_id;

   type t_employee_type is table of employee_cur%rowtype;
   t_employee t_employee_type;
   k_bulk_size constant simple_integer := 10;
begin
   open employee_cur;

   <<process_employees>>
   loop
      fetch employee_cur bulk collect into t_employee limit k_bulk_size;
      exit process_employees when employee_cur%notfound;

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

   close employee_cur;
end;
/

Example (better)

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

 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
declare
   cursor employee_cur is 
      select *
        from employee
       order by employee_id;

   type t_employee_type is table of employee_cur%rowtype;
   t_employee t_employee_type;
   k_bulk_size constant simple_integer := 10;
begin
   open employee_cur;

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

   close employee_cur;
end;
/

Example (good)

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

 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
declare
   cursor employee_cur is 
      select *
        from employee
       order by employee_id;

   type t_employee_type is table of employee_cur%rowtype;
   t_employee t_employee_type;
   k_bulk_size constant simple_integer := 10;
begin
   open employee_cur;

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

   close employee_cur;
end;
/