Votes

BULK COLLECT non-sequentially into collections (details)
 
Dear Bryn,

I love BULK COLLECT, both its performance and its simplicity. It would be so much better, though, if I could specify the column from the query that I want to use as the indexing value in the collection. Then I will not have to always fill sequentially from index value 1 - and I could even use string-indexed collections!


BULK COLLECT is a great way to query data from one or more tables into a collection. Unfortunately, it always fills the collection sequentially starting from index 1. This can be a problem when I want to actually index the data in my collection by one of the columns being queried (I want, in effect, to emulate a unique index). Today, I must BULK COLLECT the data into a temporary collection and then move it to a collection with the required index values. That's not a good use of my time or the system's CPU cycles.


Add a keyword INDEXED that flags to PL/SQL that the first (or specified) field in the select list is to be used as the index value for the collection.


DECLARE
   TYPE dept_rt IS RECORD (
      department_name   departments.department_name%TYPE
    , department_id     departments.department_id%TYPE
   );

   TYPE dept_tt IS TABLE OF dept_rt
      INDEX BY PLS_INTEGER;

   TYPE ids_by_name_tt IS TABLE OF departments.department_id%TYPE
      INDEX BY departments.department_name%TYPE;

   l_temp   dept_tt;
   l_ids    ids_by_name_tt;
BEGIN
   SELECT department_name
        , department_id
   BULK COLLECT INTO l_temp
     FROM departments;

   FOR indx IN 1 .. l_temp.COUNT
   LOOP
      l_ids (l_temp (indx).department_name) := l_temp (indx).department_id;
   END LOOP;
END;
/


DECLARE
   TYPE ids_by_name_tt IS TABLE OF departments.department_id%TYPE
      INDEX BY departments.department_name%TYPE;

   l_ids    ids_by_name_tt;
BEGIN
   SELECT department_name
        , department_id
     BULK COLLECT INTO l_ids
     INDEX ON deparment_name
     FROM departments;
END;
/


Steven Feuerstein, PL/SQL Evangelist, Quest Software: "Help me help Oracle improve the PL/SQL language!"


Bryn Llewellyn: "We love to hear from PL/SQL developers. Let us know what is important to you!"

Links

PL/SQL Obsession
Apex Evangelists
O'Reilly Books on Oracle
OTN PL/SQL Best Practices
OTN PL/SQL Page
Steven Feuerstein's Blog