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

Question

Given a query that returns a table name, is it possible to evaluate the name and use it in the next query?

eg.

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

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

Answer 1

You can, but you need to refer to the XML request .

 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_%' 

Answer 2

try using something like

 select table_name into table_name_value from ALL_TABLES where table_name like 'MY_TABLE_%'); execute immediate 'select count(1) from ' || table_name_value into returned_value; 

and take it to your cycle