Returning table names in a query 1

[Gamera99]

I have about 36 archive tables that are written to by other tables' triggers. Each of my archive tables begins with "LOG_". I am trying to develop a query that will show the count and last mod date of each table (I have a data element in each LOG table "mod_date&quot.

First I am running one query:

select table_name
from dba_tables
where table_name like 'LOG_%'
order by table_name;

then I must run a second query:

select count(*) as count,
max(mod_date) as last_mod_date
from " & rsL("table_name&quot

(rsL("table_name&quot is the recordset value from the first query; I run the second query in a loop, getting each table name from the first query.)

I can't figure out how to combine them into one query.
I want to make the first query as a subquery in Query 2
but I can't figure out how to use a table name returned from a query. Does that make sense?
Thanks if anyone has any advice;
Nelson

 

[sem]

Do you need some report? You may use sql*plus to create such "nested loop"
create script outer.sql:

set feedback off
set pages 0
set termout off

spool cmd.sql
select '@inner '||table_name
from dba_tables
where table_name like 'LOG_%'
order by table_name;
/
spool off
@cmd

-------

and inner.sql:

select '&&1', count(*) as count,
max(mod_date) as last_mod_date
from &&1
-------------
Run outer.sql

 

[Gamera99]

Well thank you but I was trying to create a query to populate a recordset on an active server page. I'm working with your response now in SQL Plus; thanks for replying to my problem.

 

[sem]

You may create stored procedure returning ref cursor, created dynamically in the same manner.
Another trick is to select num_rows from tabs, but the result data depends on gathered statistics and may be not actual.