How to create a cursor within for loop of PL/SQL Code and bulk collect results into table

Question

I have a database with many users that have the same table (by same I mean same columns but different data). I would like to run the same query on these tables and bulk collect the results into a temporary table or any other way of viewing. So far I have the code shown below:

DECLARE
  TYPE PDTABLE_12SEGTBL IS TABLE OF MTO_SG2420.PDTABLE_12%ROWTYPE;
  COLLECTIONTBL PDTABLE_12SEGTBL;
  LoopIteration pls_integer;
  CompTblName varchar2(61);

  CURSOR MTO_Cursor IS
    SELECT owner, table_name 
    FROM   ALL_TABLES 
    WHERE  OWNER LIKE 'MTO_K%' 
    AND    TABLE_NAME = 'PDTABLE_12';
BEGIN
  LoopIteration :=1;

  FOR item IN MTO_Cursor
  LOOP
    CompTblName := item.owner || '.pdtable_12';

    DBMS_OUTPUT.PUT_LINE('Loop Iteration ' || LoopIteration || ' CompTblName' || CompTblName);

    LoopIteration := LoopIteration + 1;
  END LOOP;
END;

The tables that I would like to run the query on look like this:

MTO_K01.pdtable_12
MTO_K02.pdtable_12
MTO_K03.pdtable_12
MTO_K04.pdtable_12
MTO_K05.pdtable_12

In the CompTblName variable, I store the complete table name including username successfully through each iteration. My question is how can I add a query to the code above that runs a select statement on the variable CompTblName and pushes the results into the table that I created (COLLECTIONTBL). I searched in this forum and other places and saw that I can do this using fetch command. However, fetch command needs to be placed within a cursor which gives an error every time I place it in a loop. It is important to note that I would like to concatenate the results from all iterations into COLLECTIONTBL.

Answer 1

You need to use execute immediate statement, which allows to create and run dynamic SQL:

FOR item IN MTO_Cursor LOOP
    CompTblName := item.owner || '.pdtable_12';

    execute immediate 'insert into COLLECTIONTBL select * from ' || CompTblName;
  END LOOP;