G-4120: 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.
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;
/
|