Dense and sparse collections

declare 
   type colors_t is table of varchar2(100) index by pls_integer; 
   l_colors_dense    colors_t; 
   l_colors_sparse   colors_t; 
begin 
   l_colors_dense(100) := 'Blue'; 
   l_colors_dense(101) := 'Green'; 
    
   l_colors_sparse(1) := 'Red'; 
   l_colors_sparse(3) := 'Yellow'; 
end;   

Basic for loop with dense collection

declare 
   type colors_t is table of varchar2(100) index by pls_integer; 
   l_colors colors_t := colors_t (1 => 'Blue', 2 => 'Green', 3 => 'Yellow'); 
begin 
   for indx in 1 .. l_colors.count 
   loop 
      dbms_output.put_line (l_colors (indx)); 
   end loop;   
end;   

What if index does not start at 1?

declare 
   type colors_t is table of varchar2(100) index by pls_integer; 
   l_colors colors_t; 
begin 
   l_colors (100) := 'Blue'; 
   l_colors (101) := 'Green'; 
 
   for indx in 1 .. l_colors.count 
   loop 
      dbms_output.put_line (l_colors  (indx)); 
   end loop;   
end;   

What's going on "behind the scenes"

declare 
   type colors_t is table of varchar2(100) index by pls_integer; 
   l_colors colors_t; 
begin 
   l_colors (100) := 'Blue'; 
   l_colors (101) := 'Green'; 
 
   dbms_output.put_line (l_colors (1)); 
   dbms_output.put_line (l_colors (2)); 
end;   

Using first and last

declare 
   type colors_t is table of varchar2(100) index by pls_integer; 
   l_colors colors_t; 
begin 
   l_colors(100) := 'Blue'; 
   l_colors(101) := 'Green'; 
 
   for indx in l_colors.first .. l_colors.last 
   loop 
      dbms_output.put_line (l_colors (indx)); 
   end loop;   
end;   

If collection is empty watch out for value_error!

declare 
   type colors_t is table of varchar2(100) index by pls_integer; 
   l_colors colors_t; 
begin 
   for indx in l_colors.first .. l_colors.last 
   loop 
      dbms_output.put_line (l_colors (indx)); 
   end loop;   
end; 

Protection from ORA-06502

declare 
   type colors_t is table of varchar2(100) index by pls_integer; 
   l_colors colors_t; 
begin 
   for indx in nvl (l_colors.first, 1) .. nvl (l_colors.last, 0) 
   loop 
      dbms_output.put_line (l_colors (indx)); 
   end loop;   
end; 

Avoiding no_data_found with exception handler

declare 
   type colors_t is table of varchar2(100) index by pls_integer; 
   l_colors colors_t; 
begin 
   l_colors(100) := 'Blue'; 
   l_colors(1000) := 'Green'; 
 
   for indx in l_colors.first .. l_colors.last 
   loop 
      begin 
         dbms_output.put_line (l_colors (indx)); 
      exception when no_data_found then null; 
      end; 
   end loop;   
end;   

Avoiding no_data_found with exists

declare 
   type colors_t is table of varchar2(100) index by pls_integer; 
   l_colors colors_t; 
begin 
   l_colors(100) := 'Blue'; 
   l_colors(1000) := 'Green'; 
 
   for indx in l_colors.first .. l_colors.last 
   loop 
      if l_colors.exists (indx)  
      then 
         dbms_output.put_line (l_colors (indx)); 
      end if; 
   end loop;   
end;   

Navigating with first and last

declare 
   type colors_t is table of varchar2(100) index by pls_integer; 
   l_colors colors_t; 
   l_index pls_integer; 
begin 
   l_colors(100) := 'Blue'; 
   l_colors(1000) := 'Green'; 
 
   l_index := l_colors.first; 
   while l_index is not null 
   loop 
      dbms_output.put_line (l_colors (l_index)); 
      l_index := l_colors.next (l_index); 
   end loop;   
end;   

Using SQL to iterate through tables

create or replace type list_of_names_t is table of varchar2(100);

declare
   happyfamily list_of_names_t := list_of_names_t('Kim', 'Kourtney', 'Khloe', 'Rob');
begin
   for rec in (
      select column_value family_name
        from table ( happyfamily )
       order by family_name
   ) loop
      DBMS_OUTPUT.put_line(rec.family_name);
   end loop;
end;

Sweet new syntax in Oracle Database 21c!

declare 
   type colors_t is table of varchar2(100) index by pls_integer; 
   l_colors colors_t; 
begin 
   l_colors(100) := 'Blue'; 
   l_colors(1000) := 'Green'; 
 
   for indx in indices of l_colors 
   loop 
      dbms_output.put_line (l_colors (indx)); 
   end loop;   
end;