title: How to use the table names from a select Query in the from clause of another select Query in Oracle
body: <p>I need the following output</p>
  
  <ul>
  <li>Queue Name: Name of the queue</li>
  <li>State: 0 Ready,1 Waiting, 2 Processed, 3 Expired - of the queue table</li>
  <li>Count: count the number of records in the queue table of that state.</li>
  </ul>
  
  <p>Example</p>
  
  <pre><code>Queue Name         State         Count
  ---------------------------------------
  Email_Q            Processed      5939
  Email_response_Q   Waiting         133
  </code></pre>
  
  <p>I constructed the following SQL</p>
  
  <pre><code>SELECT 'select ''' || owner || '.' || name || ''' queue_name, 
          decode(state,0,''Ready'',1,''Waiting'',2,''Processed'',3,''Expired'',''?'') state, 
          count(*) count 
          from ' || owner || '.' || queue_table || 
          ' where q_name = ''' || name || ''' group by state' cmd
    FROM all_queues
   WHERE owner IN ('AMADEUS')
   ORDER BY owner, name; 
  </code></pre>
  
  <p>I am able to construct all the selects for the Queue tables. But I would like to get the output using a select statement.</p>
  
  <p>I was thinking of pipeline functions or use of a global temporary tables.
  Any help will be much appreciated.</p>