G-4120
🆓Error
Avoid using %NOTFOUND directly after the FETCH when working with BULK OPERATIONS and LIMIT clause.
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. |
Explanation
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;
/Explanation
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;
/Explanation
This example does the trick (11 fetches only to process all rows)
References
- same as Trivadis G-4120