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

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;
/

Example (better)

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

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;
/

Example (good)

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

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;
/