Loop through multiple tables to execute same query
Posted
by
pcvnes
on Stack Overflow
See other posts from Stack Overflow
or by pcvnes
Published on 2011-03-16T14:27:14Z
Indexed on
2011/03/18
8:10 UTC
Read the original article
Hit count: 242
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
© Stack Overflow or respective owner