CODEWITHSUNDEEP
sqlloopsplsql
pcvnes
pcvnes

Reputation: 967

Loop through multiple tables to execute same query

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;
/

Upvotes: 4

Views: 40590

Answers (4)

N West
N West

Reputation: 6819

It may be better to re-architect the table to be one table with a key column on day for the process log instead of individual tables.

That being said, you can use Dynamic SQL:

DECLARE
  CURSOR all_tables IS
    SELECT table_name
            FROM all_tables 
            WHERE TABLE_NAME like 'MESSAGE_LOG_2%' 
            ORDER BY TABLE_NAME ;

  row_count pls_integer;
BEGIN
  FOR msglog IN all_tables LOOP
    EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || msglog.TABLE_NAME INTO row_count;
  END LOOP;
END;
/

Upvotes: 4

tbone
tbone

Reputation: 15473

Maybe you should put all your logs into one log table, adding a column or two to differentiate the different days (created_date or created_timestamp column maybe, + any other identifiers you wish). You could even setup partitioning if needed. Just seems an odd (and messy) way to track logs each day by creating a new table for each days logs.

Upvotes: 0

chris
chris

Reputation: 89

Use execute immediate with buld collect into http://www.adp-gmbh.ch/ora/plsql/bc/execute_immediate.html . Concatinate your tables with union all, Oracle will recognize union all to use parallel queries http://www.dba-oracle.com/art_opq1.htm .

Upvotes: 0

Neville Kuyt
Neville Kuyt

Reputation: 29629

http://download.oracle.com/docs/cd/B10500_01/appdev.920/a96590/adg09dyn.htm

basically, inside your loop, build a string representing the query you want to run, and use dynamic SQL to run the query.

To do anything useful, you're probably going to want to insert the records into a temporary table, then select ordered by by date descending.

Upvotes: 1

Related Questions