The difference between using First..Last and 1..count in Oracle

data collectionoracleplsql

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

Best Answer

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
Related Question