What is the difference between using First..Last and 1..count in Oracle?

Question

What is the difference between using First..Last and 1..count in Oracle?

FOR i IN l_collection.FIRST..l_collection.LAST LOOP

or

FOR i IN 1..l_collection.COUNT LOOP

Answer 1

first and last returns the first index and last index. count returns the number of elements in the collection. These do not necessarily give a correct way of referencing the elements in the collection, as you can have sparse collections, where index values are not contiguous (see below example).

declare
  type t_numbers is table of number index by pls_integer;
  l_numbers t_numbers;
begin
  l_numbers(1) := 5;
  l_numbers(4) := 6;
  for i in l_numbers.first..l_numbers.last loop
    dbms_output.put_line(i);
  end loop;
end;
/

1
2
3
4

declare
  type t_numbers is table of number index by pls_integer;
  l_numbers t_numbers;
begin
  l_numbers(1) := 5;
  l_numbers(4) := 6;
  for i in 1..l_numbers.count loop
    dbms_output.put_line(i);
  end loop;
end;
/

1
2

I hope you see why both versions fail. These work properly only as long as you have dense collections (where index values are contiguous). With sparse collections, both method fail, and you should use a WHILE loop:

declare
  type t_numbers is table of number index by pls_integer;
  l_index number;
  l_numbers t_numbers;
begin
  l_numbers(1) := 5;
  l_numbers(4) := 6;
  l_index := l_numbers.first;
  while (l_index is not null)
  loop
    dbms_output.put_line(l_index);
    l_index := l_numbers.next(l_index);
  end loop;
end;
/

1
4

Answer 2

1 .. count is better because you can use it when a collection is empty. first .. last will raise an exception for an empty collection.

Here is a good article on it.

The precondition for using 1 .. count (and also first .. last) is that the nested table may not be sparse and it must start at 1.

This is true whenever you use BULK COLLECT which I find to be the most common scenario.

---

For sparse associative arrays, use a while loop instead:

l_index := names_in.FIRST;
  WHILE (l_index IS NOT NULL)
  LOOP
    DBMS_OUTPUT.PUT_LINE 
       (names_in(l_index)); 
    l_index := names_in.NEXT 
       (l_index);
  END LOOP;