2

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
  • If you create an index by table, and you have your index starting by 1, there's no difference. But you can create a collection by some other value. That way, would be better to use first.. last – Renato Afonso Feb 1 '17 at 10:59
4

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.