title: EXECUTE IMMEDIATE results into Table Type
body: <p>I need to have an EXECUTE IMMEDIATE return its results into an table type (AS TABLE OF)</p>
  
  <p>If the execute immediate returns a single value i am able to save the result using "INTO somevariable"</p>
  
  <p>But, my execute immediate will return a multi column table and no amount of googling have help me figure it out!</p>
  
  <p>First I create the types</p>
  
  <pre><code>CREATE OR REPLACE TYPE T_VALIDITY_RECORD AS OBJECT (
     TIME_COL  DATE,
     VALUE_COL NUMBER
  );
  /
  
  CREATE OR REPLACE TYPE T_VALIDITY_TABLE AS TABLE OF T_VALIDITY_RECORD;
  /
  </code></pre>
  
  <p>Then I attempt the code</p>
  
  <pre><code>DECLARE
     RET_TABLE   T_VALIDITY_TABLE;
  BEGIN
  
  EXECUTE IMMEDIATE 'SELECT my_date,
                            my_numbers  
                     FROM   my_table
                     WHERE  somthing = somthingelse' INTO RET_TABLE;
  END;
  </code></pre>
  
  <p>This is just a really simplified example, the real code does (will do other things)</p>
  
  <p>I've tried various things like BULK COLLECT INTO etc but can't seem to get it working.</p>
  
  <p>I am getting the following error:</p>
  
  <blockquote>
    <p>Error report<br>
    ORA-00932: inconsistent datatypes: expected - got -<br>
    ORA-06512: at line 5<br>
    00932. 00000 -  "inconsistent datatypes: expected %s got %s"<br>
    *Cause:<br>
    *Action:</p>
  </blockquote>