“What is the best way to process a dense nested table?” seems to be a simple question with an quite simple answer…
Looping First to Last
One way to implement this kind of loop is making use of the collection methods first and last to write code like:
begin
for i in myArray.first() .. myArray.last() loop
... do whatever you need to do
end loop;
end;
This works as long as the array is initialized and not empty.
If the array is not initialized you get a
ORA-06531: Reference to uninitialized collection
exception. This because the first/last methods can only be used on an initialized collection. If the collection is initilized but empty, the exception thrown is:
ORA-06502: PL/SQL: numeric or value error
the reason for this error is, that on an empty array first and last return null as the result and this is not feasible for a numeric for loop.
So the correct implemetation using the first/last version of a loop would be:
begin
if myArray is not null
and myArray is not empty
then
for i in myArray.first() .. myArray.last() loop
... do whatever you need to do
end loop;
end if;
end;
but…we can do better (and shorter).
Looping 1 to count
A slightly different approach is to use 1 as the lower and count as the upper border. If count is called on an empty collection we get 0 as result which has the effect that the loop body is never executed. But as with last/first we get an error when count is used on a collection that has not been initialized.
So the solution using 1..count would look like this:
begin
if myArray is not null
then
for i 1 .. myArray.count() loop
... do whatever you need to do
end loop;
end if;
end;
but again…we can do better (and shorter).
Looping using cardinality
Instead of using count to get the number of entries of a nested table we can also use the cardinality function. The advantage of cardinality is that this function can be called on not initialized collections too. The result of cardinality in this case would be null. Null however is not a valid value for a loop border so the cardinality call has to be surrounded by a nvl/coalesce function.
begin
for i 1 .. nvl(cardinality(myArray),0) loop
... do whatever you need to do
end loop;
end;
This is the shortes way to loop over a collection and it is a version that handles not initialized and empty collections too.