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….
….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
Even before 21c, we can do a single cursor FOR loop, relying on the array optimization of PL/SQL, like this:
Cheers & Best Regards,
Thanks for pointing that out, Iudith. That is a better “before 21c” solution than the one I show in the post.