By: Steven Feuerstein On: July 13, 2022 In: Uncategorised Comments: 2

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:
Share

2 Comments:

    • Iudith Mentzel
    • July 13, 2022
    • Reply

    Hello Steven,

    Even before 21c, we can do a single cursor FOR loop, relying on the array optimization of PL/SQL, like this:

    declare
       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
         for rec in (select employee_id, last_name
                             from employees) 
         loop
               ids (rec.last_name) := rec.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;

    Cheers & Best Regards,

    Iudith

      • Steven Feuerstein
      • July 14, 2022
      • Reply

      Thanks for pointing that out, Iudith. That is a better “before 21c” solution than the one I show in the post.

      Steven

Leave reply:

Your email address will not be published.