Skip to content

G-4360: Always use a WHILE loop to process a loose array.

Critical

Efficiency

Reason

When a loose (also called sparse) array is processed using a numeric for loop we have to check with all iterations whether the element exist to avoid a no_data_found exception. In addition, the number of iterations is not driven by the number of elements in the array but by the number of the lowest/highest element. The more gaps we have, the more superfluous iterations will be done.

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
12
13
14
15
16
17
18
19
20
declare -- raises no_data_found when processing 2nd record
   type t_employee_type is table of employees.employee_id%type;
   t_employees    t_employee_type;
   co_rogers      constant integer := 134;
   co_matos       constant integer := 143;
   co_mcewen      constant integer := 158;
   co_index_matos constant integer := 2;
begin
   t_employees := t_employee_type(co_rogers,co_matos,co_mcewen);
   t_employees.delete(co_index_matos);

   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));
      end loop process_employees;
   end if;
end;
/

Example (good)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
declare
   type t_employee_type is table of employees.employee_id%type;
   t_employees    t_employee_type;
   co_rogers      constant integer := 134;
   co_matos       constant integer := 143;
   co_mcewen      constant integer := 158;
   co_index_matos constant integer := 2;
   l_index        pls_integer;
begin
   t_employees := t_employee_type(co_rogers,co_matos,co_mcewen);
   t_employees.delete(co_index_matos);

   l_index     := t_employees.first();

   <<process_employees>>
   while l_index is not null
   loop
      sys.dbms_output.put_line(t_employees(l_index));
      l_index := t_employees.next(l_index);
   end loop process_employees;
end;
/