title: How to use EXECUTE IMMEDIATE to populate temp table in Oracle
body: <p>I have a number of possible queries that could execute all of which will return two columns. Which queries actually run isn't known ahead of the procedure being called. I need to act on the data returned in these queries before placing them into another, known, table. </p>
  
  <p>So I have created a table type to store these two records. I fetch the query, use EXECUTE IMMEDIATE, and attempt to use bulk collect into. Like so:</p>
  
  <pre><code>create or replace TYPE TEMP_RECORD IS object (
     Identifier VARCHAR2(5000), 
     Message varchar2(5000)
    );
    /
  create or replace TYPE THETEMP_TABLE IS TABLE OF TEMP_RECORD;
  </code></pre>
  
  <p>and</p>
  
  <pre><code>DECLARE
    ID NUMBER(38);
    runID NUMBER(38);
    sqlToRun varchar(5000);
    tempTable THETEMP_TABLE;
    CURSOR myCursor IS 
    select ID, SQL FROM QueryTables  
    where IsActive = 1; 
  
  BEGIN
  
      OPEN myCursor;
      LOOP
      FETCH myCursor INTO id,sqlToRun;
  
          EXIT WHEN myCursor%notfound; 
  
          INSERT INTO Run(RunID,StartDateTime)
          VALUES (id,(select sysdate from dual))
          RETURNING ID INTO runID;
  
         -- dbms_output.PUT_LINE(runID);
          EXECUTE IMMEDIATE sqlToRun BULK COLLECT INTO tempTable;
  
          INSERT INTO RunResults(RunDate,RunID,SourceID,ResultDescription)
          SELECT (select sysdate from dual),runID, Identifier, Message FROM tempTable;
  
  
      END LOOP; 
      CLOSE myCursor ;
  
  END;
  </code></pre>
  
  <p>The problem is here:
  <code>EXECUTE IMMEDIATE sqlToRun BULK COLLECT INTO tempTable;</code></p>
  
  <p>I get this error:</p>
  
  <pre><code>ORA-00932: inconsistent datatypes: expected - got -
  ORA-06512: at line 23
  00932. 00000 -  "inconsistent datatypes: expected %s got %s"
  </code></pre>
  
  <p>I believe the datatypes are correct. I am guessing this is an error which how I am attempting to do it.</p>
  
  <p><strong>EDIT:</strong> An example of one of those dynamic queries. </p>
  
  <pre><code>SELECT c.CustomerIdentifier as SourceIdentifier,'Invalid Customer Type record' as InvalidFields
  FROM Customer c
  WHERE 
  c.CustomerType &lt;&gt; 'a' and c.CustomerType &lt;&gt; 'b' and c.CustomerType &lt;&gt; 'b'
  </code></pre>
  
  <p>So: <strong>How do I populate temporary table from a dynamic query which returns a known amount of columns of the same datatype?</strong> </p>