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