Skip to content

G-4350: Always use 1 as lower and COUNT() as upper bound when looping through a dense array.

Blocker

Reliability

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

Example (bad)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
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;
/

Example (better)

Raise an unitialized collection error if t_employees is not initialized.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
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;
/

Example (good)

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.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
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;
/