Populating string-indexed collections from tables

Sam Nitsche recently interviewed me for an article in the DOAG (German Oracle User Group) RedStack magazine. That was fun! You can find the entire article in English on Sam’s blog. I especially love the title Sam used for his post:

Stop Hiding From Your Planet

In any case, buried in that interview is this comment:

One very specific feature that I would love to have them [Oracle] add in is the ability to select bulk-collect into string indexed collections. Something like “select bulk collect into index by” and then give a column name (or several) to do the indexing.

In other words, I’d love to be able to do this:

declare
   type ids_by_name_t is
      table of number index by varchar2(20);
   ids ids_by_name_t;
begin
   select employee_id bulk collect into ids
     from employees
 index by last_name;

   for indx in indices of ids loop
      dbms_output.put_line(indx || ' = ' || ids(indx));
   end loop;
end;

That is not, unfortunately, supported by the otherwise heavenly PL/SQL language.

As a result, we have to write code along these lines: grab the data from the table(s) and load them into an integer-indexed collection. Then move it over to a string-indexed collection.

declare
   -- integer-indexed nested table
   type emp_rows_t is table of employees%rowtype;
   l_emp_rows emp_rows_t;

   -- string-indexed collection
   type ids_by_name_t is
      table of number index by varchar2(20);
   ids        ids_by_name_t;
   l_index    employees.last_name%type;
begin
   select *
   bulk collect
     into l_emp_rows
     from employees;

   for indx in 1..l_emp_rows.count loop
      ids(l_emp_rows(indx).last_name) := l_emp_rows(indx).employee_id;
   end loop;

   l_index := ids.first;
   while l_index is not null loop
      dbms_output.put_line(l_index
                           || ' = '
                           || ids(l_index));
      l_index := ids.next(l_index);
   end loop;
end;

Well. That’s the way you’d have to do it prior to Oracle Database 21c, anyway. How do I know that? Because after reading my article, Connor McDonald very politely DM’d me on Twitter to remind me that the following is now possible:

declare
   type ids_by_name_t is
      table of number index by varchar2(20);
   ids ids_by_name_t;
begin
   ids := ids_by_name_t(
            for I in (select employee_id, last_name
                        from employees) 
               index i.last_name => i.employee_id);

   for indx in indices of ids loop
      dbms_output.put_line(indx
                           || ' = '
                           || ids(indx));
   end loop;
end;

And that, my friends, is some really nice simplification. I’m still holding out for the “real deal” – a select bulk collect into with an index by clause. But if I am lucky enough to be on a 21c database, I’ll be sure to make the most of all the new iterator magic the PL/SQL team has added to the language.

Learn more from the official documentation….

PL/SQL Extended Iterators

….and Chris Saxon and Charles Wetherell….

Better loops and qualified expressions (array constructors) in PL/SQL

….and Connor McDonald….

PL/SQL in 21c gets amazing new loop iterators

….and Tim Hall….

FOR LOOP Iteration Enhancements in Oracle Database 21c

Share this: