Generate code to move rows into a string-indexed collection

View All Scripts Login to Run Script

Script Name Generate code to move rows into a string-indexed collection
Description We'd all love to be able to do something like "SELECT * BULK COLLECT INTO x FROM employees INDEX BY last_name" but that's not a part of Oracle Database. So I thought I'd build a little procedure to generate the code you need to do this. I offer two approaches: first, one that bulk collects into an integer-indexed collection and then move to string-indexed. The second is simpler and uses a cursor FOR loop to populate the string-indexed collection.
Area PL/SQL General
Contributor Steven Feuerstein
Created Wednesday August 12, 2020

Statement 1

CREATE OR REPLACE PROCEDURE gen_select_into_index_by1 (query_in    IN VARCHAR2,
                                                      table_in    IN VARCHAR2,
                                                      column_in   IN VARCHAR2)
IS
   c_template VARCHAR2(32767)
      := q'[DECLARE
   CURSOR c IS #QUERY#;

   TYPE t IS TABLE OF c%ROWTYPE;

   c_limit CONSTANT PLS_INTEGER := 100;
   l_rows_by_integer    t;

   TYPE vct IS TABLE OF c%ROWTYPE INDEX BY VARCHAR2(32767);

   l_rows_by_varchar2   vct;
   l_index VARCHAR2(32767);
BEGIN
   OPEN c;

   LOOP
      FETCH c BULK COLLECT INTO l_rows_by_integer LIMIT c_limit;
      EXIT WHEN l_rows_by_integer.COUNT = 0;

      FOR indx IN 1 .. l_rows_by_integer.COUNT
      LOOP
         l_rows_by_varchar2 (l_rows_by_integer (indx).#COLUMN#) 
            := l_rows_by_integer (indx);
      END LOOP;
   END LOOP;
   
   CLOSE c;

   /* Iterate through the string-indexed collection */
   
   l_index := l_rows_by_varchar2.FIRST;
   
   WHILE l_index IS NOT NULL
   LOOP
      DBMS_OUTPUT.PUT_LINE (l_rows_by_varchar2(l_index).#COLUMN#);
      l_index := l_rows_by_varchar2.NEXT (l_index);
   END LOOP;
END;]';
BEGIN
   DBMS_OUTPUT.put_line (
      REPLACE (
         REPLACE (
            REPLACE (c_template, '#QUERY#', query_in),
            '#TABLE#', table_in),
         '#COLUMN#', column_in));
END;

Procedure created.

Statement 2

begin
gen_select_into_index_by1 (
   'select last_name, first_name, employee_id 
      from hr.employees where department_id = 30', 'hr.employees', 'last_name');
end;

Statement processed.
DECLARE
   CURSOR c IS select last_name, first_name, employee_id 
      from hr.employees where department_id = 30;

   TYPE t IS TABLE OF c%ROWTYPE;

   c_limit CONSTANT PLS_INTEGER := 100;
   l_rows_by_integer    t;

   TYPE vct IS TABLE OF c%ROWTYPE INDEX BY VARCHAR2(32767);

   l_rows_by_varchar2   vct;
   l_index VARCHAR2(32767);
BEGIN
   OPEN c;

   LOOP
      FETCH c BULK COLLECT INTO l_rows_by_integer LIMIT c_limit;
      EXIT WHEN l_rows_by_integer.COUNT = 0;

      FOR indx IN 1 .. l_rows_by_integer.COUNT
      LOOP
         l_rows_by_varchar2 (l_rows_by_integer (indx).last_name) 
            := l_rows_by_integer (indx);
      END LOOP;
   END LOOP;
   
   CLOSE c;

   /* Iterate through the string-indexed collection */
   
   l_index := l_rows_by_varchar2.FIRST;
   
   WHILE l_index IS NOT NULL
   LOOP
      DBMS_OUTPUT.PUT_LINE (l_rows_by_varchar2(l_index).last_name);
      l_index := l_rows_by_varchar2.NEXT (l_index);
   END LOOP;
END;

Statement 3

What if you want to index by an expression involving more than one column? Add that expression to the query, give it a name, and that's the column you pass in!

Index by an expression

begin 
gen_select_into_index_by1 ( 
   'select last_name, first_name, last_name||''-''||first_name combined_name, employee_id  
      from employees where department_id = 30', 'employees', 'combined_name'); 
end;

Statement processed.
DECLARE
   CURSOR c IS select last_name, first_name, last_name||'-'||first_name combined_name, employee_id  
      from employees where department_id = 30;

   TYPE t IS TABLE OF c%ROWTYPE;

   c_limit CONSTANT PLS_INTEGER := 100;
   l_rows_by_integer    t;

   TYPE vct IS TABLE OF c%ROWTYPE INDEX BY VARCHAR2(32767);

   l_rows_by_varchar2   vct;
   l_index VARCHAR2(32767);
BEGIN
   OPEN c;

   LOOP
      FETCH c BULK COLLECT INTO l_rows_by_integer LIMIT c_limit;
      EXIT WHEN l_rows_by_integer.COUNT = 0;

      FOR indx IN 1 .. l_rows_by_integer.COUNT
      LOOP
         l_rows_by_varchar2 (l_rows_by_integer (indx).combined_name) 
            := l_rows_by_integer (indx);
      END LOOP;
   END LOOP;
   
   CLOSE c;

   /* Iterate through the string-indexed collection */
   
   l_index := l_rows_by_varchar2.FIRST;
   
   WHILE l_index IS NOT NULL
   LOOP
      DBMS_OUTPUT.PUT_LINE (l_rows_by_varchar2(l_index).combined_name);
      l_index := l_rows_by_varchar2.NEXT (l_index);
   END LOOP;
END;

Statement 4

Generate Less Code Using Cursor FOR Loop

CREATE OR REPLACE PROCEDURE gen_select_into_index_by2 (query_in    IN VARCHAR2, 
                                                      table_in    IN VARCHAR2, 
                                                      column_in   IN VARCHAR2) 
IS 
   c_template   VARCHAR2(32767) 
      := q'[DECLARE 
   CURSOR c IS #QUERY#; 
   
   TYPE vct IS TABLE OF c%ROWTYPE INDEX BY VARCHAR2(32767); 
 
   l_rows_by_varchar2   vct; 
   l_index VARCHAR2(32767); 
BEGIN 
   FOR rec IN (#QUERY#) 
   LOOP 
      l_rows_by_varchar2 (rec.#COLUMN#) := rec; 
   END LOOP; 
    
   CLOSE c; 
 
   /* Iterate through the string-indexed collection */ 
    
   l_index := l_rows_by_varchar2.FIRST; 
    
   WHILE l_index IS NOT NULL 
   LOOP 
      DBMS_OUTPUT.PUT_LINE (l_rows_by_varchar2(l_index).#COLUMN#); 
      l_index := l_rows_by_varchar2.NEXT (l_index); 
   END LOOP; 
END;]'; 
BEGIN 
   DBMS_OUTPUT.put_line ( 
      REPLACE ( 
         REPLACE ( 
            REPLACE (c_template, '#QUERY#', query_in), 
            '#TABLE#', table_in), 
         '#COLUMN#', column_in)); 
END;

Procedure created.

Statement 5

begin 
gen_select_into_index_by2 ( 
   'select last_name, first_name, employee_id  
      from employees where department_id = 30', 'employees', 'last_name'); 
end;

Statement processed.
DECLARE 
   CURSOR c IS select last_name, first_name, employee_id  
      from employees where department_id = 30; 
   
   TYPE vct IS TABLE OF c%ROWTYPE INDEX BY VARCHAR2(32767); 
 
   l_rows_by_varchar2   vct; 
   l_index VARCHAR2(32767); 
BEGIN 
   FOR rec IN (select last_name, first_name, employee_id  
      from employees where department_id = 30) 
   LOOP 
      l_rows_by_varchar2 (rec.last_name) := rec; 
   END LOOP; 
    
   CLOSE c; 
 
   /* Iterate through the string-indexed collection */ 
    
   l_index := l_rows_by_varchar2.FIRST; 
    
   WHILE l_index IS NOT NULL 
   LOOP 
      DBMS_OUTPUT.PUT_LINE (l_rows_by_varchar2(l_index).last_name); 
      l_index := l_rows_by_varchar2.NEXT (l_index); 
   END LOOP; 
END;

Statement 6

begin 
gen_select_into_index_by2 ( 
   'select last_name, first_name, last_name||''-''||first_name combined_name, employee_id  
      from employees where department_id = 30', 'employees', 'combined_name'); 
end;

Statement processed.
DECLARE 
   CURSOR c IS select last_name, first_name, last_name||'-'||first_name combined_name, employee_id  
      from employees where department_id = 30; 
   
   TYPE vct IS TABLE OF c%ROWTYPE INDEX BY VARCHAR2(32767); 
 
   l_rows_by_varchar2   vct; 
   l_index VARCHAR2(32767); 
BEGIN 
   FOR rec IN (select last_name, first_name, last_name||'-'||first_name combined_name, employee_id  
      from employees where department_id = 30) 
   LOOP 
      l_rows_by_varchar2 (rec.combined_name) := rec; 
   END LOOP; 
    
   CLOSE c; 
 
   /* Iterate through the string-indexed collection */ 
    
   l_index := l_rows_by_varchar2.FIRST; 
    
   WHILE l_index IS NOT NULL 
   LOOP 
      DBMS_OUTPUT.PUT_LINE (l_rows_by_varchar2(l_index).combined_name); 
      l_index := l_rows_by_varchar2.NEXT (l_index); 
   END LOOP; 
END;

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database