I have a query that returns table names I need, for example
select table_name from all_tables where lower(table_name) like 'test_table%';
How do I perform a select from all those tables by their names that I receive as result of the query? I tried concatenate them and use "execute immediate", but I couldn't make it work.
I tried:
execute immediate 'select value from '|| select listagg(table_name, ', ') within group (order by table_name) from all_tables where table_name like 'test_table%';