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

Filed under:
|
|
|

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

Related posts about sql

Related posts about table