G-4350
🆓Error
Always use 1 as lower and COUNT() as upper bound when looping through a dense array.
Reason
Doing so will not raise a value_error if the array you are looping through is empty. If you want to use first()..last() you need to check the array for emptiness beforehand to avoid the raise of value_error.
Please note that:
- Varrays are always dense
- Associative arrays (or index-by tables) are either dense or sparse
- Nested tables start dense and may become sparse
Examples
Non-Compliant Example
declare
type t_employee_type is table of employees.employee_id%type;
t_employees t_employee_type := t_employee_type();
begin
<<process_employees>>
for i in t_employees.first()..t_employees.last()
loop
sys.dbms_output.put_line(t_employees(i)); -- some processing
end loop process_employees;
end;
/Issues
| Line | Column | Message |
|---|---|---|
| 6 | 13 | Always use 1 as lower and COUNT() as upper bound when looping through a dense array. |
Compliant Solution - ★★★☆☆
declare
type t_employee_type is table of employees.employee_id%type;
t_employees t_employee_type := t_employee_type();
begin
<<process_employees>>
for i in 1..t_employees.count()
loop
sys.dbms_output.put_line(t_employees(i)); -- some processing
end loop process_employees;
end;
/Explanation
Raise an unitialized collection error if t_employees is not initialized.
Compliant Solution - ★★★★★
declare
type t_employee_type is table of employees.employee_id%type;
t_employees t_employee_type := t_employee_type();
begin
if t_employees is not null then
<<process_employees>>
for i in 1..t_employees.count()
loop
sys.dbms_output.put_line(t_employees(i)); -- some processing
end loop process_employees;
end if;
end;
/Explanation
Raises neither an error nor checking whether the array is empty. t_employees.count() always returns a number (unless the array is not initialized). If the array is empty count() returns 0 and therefore the loop will not be entered.
References
- same as Trivadis G-4350