Oracle: select * from (select table_name from ... )?

Question

Given a query that returns the name of tables, is it possible to evaluate the name and use it in a subsequent query?

E.G.

select count(1) from x where x in 
    (select table_name from ALL_TABLES where table_name like 'MY_TABLE_%');

Obviously this is invalid syntax, but it should illustrate what I'm trying to do.

Answer 1

You can, but it requires that you resort to an XML query.

select
       table_name,
       to_number(
         extractvalue(
           xmltype(
             dbms_xmlgen.getxml('select count(*) c ' ||
                                ' from '||owner||'.'||table_name))
           ,'/ROWSET/ROW/C')) count
   from all_tables
  where table_name like 'MY_TABLE_%'