Loop through multiple tables to execute same query

Question

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

If you are in a position to change the database schema and application logic, it would be much better to use a single table with a date column. — Blorgbeard, Mar 16 '11 at 14:29

N West · Answer 1 · 2023-03-01T16:45:08.007

4

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

edited Mar 01 '23 at 16:45

answered Mar 16 '11 at 19:41

N West

6,768
25
40

i know, but have no influence on how the customer designs – pcvnes Mar 18 '11 at 08:10
works for me if I use single quotation marks on the line: `EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || msglog.TABLE_NAME INTO row_count;` – DannyDannyDanny Feb 26 '23 at 12:10

score 1 · Accepted Answer · answered Mar 16 '11 at 14:30

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.

score 0 · Answer 3 · answered Mar 16 '11 at 20:34

0

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 .

answered Mar 16 '11 at 20:34

chris

89
6

score 0 · Answer 4 · answered Mar 17 '11 at 11:59

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.

Loop through multiple tables to execute same query

4 Answers4

Linked