title: Insert and select from a table/array in oracle
body: <p>I have a pretty big SQL statement which returns a list of id's. I need this id-list as base for other statements. Like this:</p>
  
  <pre><code>open crs_result1 for ' select * from ... where id in ( select &lt;ids&gt; from &lt;base_statement&gt; ) ';
  open crs_result2 for ' select * from ... where id in ( select &lt;ids&gt; from &lt;base_statement&gt; ) ';
  open crs_result3 for ' select * from ... where id in ( select &lt;ids&gt; from &lt;base_statement&gt; ) ';
  ...
  </code></pre>
  
  <p>Of course I don't want to select the whole id-list each time for the different selects.</p>
  
  <p>So, my idea was to use a table/array:</p>
  
  <pre><code>TYPE gt_result_rec IS RECORD
  (
      id NUMBER
  );
  TYPE gt_result_tab IS TABLE OF gt_result_rec INDEX BY BINARY_INTEGER;
  
  t_results gt_result_tab;
  
  execute immediate 'insert into t_results select &lt;ids&gt; from &lt;base_statement&gt;';
  </code></pre>
  
  <p>And than use it for all other statements:</p>
  
  <pre><code>open crs_result1 for ' select * from ... where id in ( select id from t_results ) ';
  ...
  </code></pre>
  
  <p>But this doesn't really work.</p>
  
  <p>Does anyone know the problem OR has a better solution for this?</p>