Loop through multiple tables to execute same query
- by pcvnes
Hi,
I have a database wherein per day a table is created to log process instances.
The tables are labeled MESSAGE_LOG_YYYYMMDD
Currently i want to sequentially execute the same QUERY against all those tables.
I wrote the PL/SQL below, but got stuck at line 10. How can i execute the SQL statement against successfully against all tables here ?
DECLARE
CURSOR all_tables IS
SELECT table_name
FROM all_tables
WHERE TABLE_NAME like 'MESSAGE_LOG_2%'
ORDER BY TABLE_NAME ;
BEGIN
FOR msglog IN all_tables LOOP
SELECT count(*) FROM TABLE msglog.TABLE_NAME ;
END LOOP;
END;
/
Cheers,
Peter
