Parsing Results Of Dynamically Built SELECT Query

When you want (from whatever reason) parse data coming from dynamically built SELECT query you have to use EXECUTE IMMEDIATE and BULK COLLECT together with collections and records.

See the sample below and feel free to adjust according to your needs

DECLARE 

 -- this record will contain data for your columns selected in the query below
 TYPE emp_rec IS RECORD (
     emp_name_data VARCHAR2(255)
    ,department_data VARCHAR2(255)
   ); 
 
 -- create collection  
 TYPE emp_tbl IS TABLE OF emp_rec INDEX BY PLS_INTEGER;
 
 -- init instance
 l_employees  EMP_TBL;
 
BEGIN
  
  FOR i IN ( -- get your column list, table name and owner name 
              SELECT owner
              , table_name
              , listagg(column_name, ', ') within group (order by owner, table_name) as coll_list
              FROM dba_tab_columns
              WHERE 1=1
              AND table_name = 'EMPLOYEE'
              AND column_name IN ('EMP_NAME','DEPARTMENT') -- this has to be defined in the record above
              GROUP BY owner
              , table_name
           )
  LOOP
      -- parse data and bulk collect it
      EXECUTE IMMEDIATE 'SELECT '|| i.coll_list || ' FROM '||i.owner ||'.'||i.table_name 
                 BULK COLLECT INTO l_employees ;
      
      -- loop through
      FOR x IN 1 .. l_employees.count
      LOOP
         dbms_output.put_line(i.owner||'.'||i.table_name||' -> '||l_employees(x).emp_name_data||':'||l_employees(x).department_data);
      END LOOP;
      
  END LOOP;
END;

There are several other solutions you can use; I prefer this 🙂 it’s fast and efficient