Feuertip #14: Iterating through collections

Sparse and Dense Collections

A collection is dense (usually as a result of being sequentially filled) if every index value between the lowest and highest is defined.

A collection is sparse if there is at least one index value between the lowest and highest that is not defined.

In the block below, I declare two associative arrays and fill one densely and one sparsely.

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; 

A few things to remember:
• A dense collection’s lowest index value need not be 1.
• The way you iterate through a collection is affected greatly by whether it is dense or sparse.
• You should never assume a collection is dense unless it was filled in a way that guarantees it is dense.
• When you fill a collection using bulk collect, it always fills the collection sequentially from 1.
• When you fill a collection using a constructor function (for nested table or varray), it always fills the collection sequentially from 1.
• String-indexed associative arrays are always sparse.

Iterating Through Dense Collections

When you are certain that your collection will always be either empty or densely filled, you can use a numeric for loop to iterate through that collection.

[Note: in the block below, I use a qualified expression to initialize the collection. This feature was first added in 18.1.]

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; 

The count method returns the number of elements. Since it is densely filled starting from 1, the “1 to count” format is a perfect fit.

But what if the collection is dense, but does not necessarily start at index value 1? When I run the code it fails with an “ORA-01403: no data found” exception!

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; 

So, first, yes, that is confusing. The PL/SQL engine raises the same exception for trying to “read” a “row” in a collection that does not exist as it does for a select-into that does not find any rows.

As to why the engine ran into this problem, the above block could also be written as:

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;   

Which makes it crystal clear that we are trying to look at the wrong indexes.

When you are not certain that your collection is populated sequentially from 1, you should use the first and last methods as in:

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;   

And now you might be thinking: “Why not use first and last all the way? That will work for collections filled sequentially from 1 as well, correct?”

Correct. But there is a complication. What if my collection is empty? In this case, the following for loop header works great:

for indx in 1 .. l_colors.count

The count method returns 0, 1 to 0 does nothing.

But consider:

for indx in l_colors.first .. l_colors.last

If l_colors is empty then both first and last return null. And, generally, in PL/SQL, if the low or high expressions in a for loop evaluate to null, the engine raises “ORA-06502: PL/SQL: numeric or value error”.

So if your densely-filled collection is not populated from 1 and might be empty and you want to use a numeric for loop, it should look like some variation on this:

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; 

There is another reason to not use first and last when you are iterating through a collection that you know is either empty or filled from index 1: the code is misleading and could hide errors.

Iterating Through Sparse Collections

OK so now let’s take a look at how to iterate through collections that are sparse. Reminder: if I try to “read” an element at an index value that does not exist, I will get the ORA-01403 error (aka, no_data_found). So this is a no-no:

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
      dbms_output.put_line (l_colors (indx));
   end loop;  
end;  

Of course, if I really want to use a numeric for loop with a sparse collection, I can. It’s just…well…ugly. And potentially really slow:

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; 
</pre.

I can also take advantage of the exists method:

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;   

But seriously if your collection is, or may be, sparse, you should avoid for loops and go with a while loop built around navigation methods, as in:

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; 

If you want to go in reverse order through the collection, use last instead of first and prior instead of next.

Oh, and please, please, please don’t forget that call to next (or prior) at the end of the loop. Leave it out, and you will have an infinite loop.

I know this. I have done this. Many times.

You might then argue that we should always use a while loop and never a for loop since this algorithm will work for dense as well as sparse collections. Two issues:

1. It’s more code, and why would you decide to write more code when it is not necessary?
2. If your collection is supposed to be dense and then something in your code changes it to sparse, you’ll never know it. In other words, again, you could mask a bug.

The best rule to follow is:

Use the approach that most closely adheres to the facts of your application.

Iterating with SQL

What about SQL? It’s really to “iterate” through rows in a table. You do not write an algorithm. You simply write a select statement that describes the data you want. That’s really nice stuff. And yes you can do it with collections as well!

Oracle SQL allows you to treat a collection as if it were a relational table. This has been possible for a looong time for nested tables and varrays, using the table operator, as in:

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;
/

As of 12.1, you can also select from integer-indexed associative arrays (their types must be defined in a package specification), and you can even leave off the table operator (for all types of collections).

That is very cool stuff.

Note: I would not use a select as my go-to choice for collection iteration, over a for loop or while loop. SQL is cool, but there is a price to be paid in performance, compared to simply iterating through my collection, stored in session memory (PGA), all within the PL/SQL engine.

But if you need to sort collection contents or join in-session-memory collection data with persistent table data, this approach is absolutely the way to go!

Oracle Database 21c Iterators

Finally, some very exciting news in Oracle Database 21c: life gets so much easier when it comes to iterators. Not just for collections but for all kinds of data structures. And, well, life can also get way more complicated, but only if you have very complex requirements.

In 21.1, Oracle PL/SQL “catches up” with other programming languages when it comes to iterators. It’s a big topic and beyond the scope of this post, but you will now (when you get 21c installed) be able to iterate through a collection with nothing more than this:

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;   

For more about 21c iterators and other 21c features:

Start with the doc.

Connor McDonald explores them in a blog post here.

Chris Saxon covers new 21c iterators and lots of other new features in 21c in this AskTOM Office Hours video.

And, of course, Tim Hall of ORACLE-BASE, does us all proud with a clear, handy guide to this great feature here.

It Should Be Simpler?

Well, not as of 21c, it shouldn’t. Because in 21c it is simpler. But many developers do not yet have the privilege and honor of using this recent release of Oracle Database. So until then, you have choices to make, and I hope this post helps you make them correctly, and with confidence.

Oh and if you’d like to explore the code in this post, check out my Oracle LiveSQL script.