0

I need to fire the same query on multiple tables. Query might return zero, one or more number of rows.

I can loop through the tables using EXECUTE IMMEDIATE but for returning multiple rows I would need a datatype so I think I would need to keep it as CURSOR.

for ease, lets say I need to execute below query on 2 tables - table1 and table2

Table1 has following columns

datetime
device_name
value1
value2

Table2 has following columns

datetime
device_name
value3
value4

Query to be executed on both the tables as below:

select datetime, count(*) from table_name group by datetime;

Whats the best approach here?

please note that I can't create any DB objects (proc/function). Has to be anonymous block only.

Kapil
  • 149
  • 1
  • 13

1 Answers1

2

As long as the cursor structures are the same, you can loop through with some dynamic ref cursors, eg

SQL> set serverout on
SQL> declare
  2    tablist sys.odcivarchar2list :=
  3      sys.odcivarchar2list('ALL_OBJECTS','USER_OBJECTS');
  4    rc sys_refcursor;
  5
  6    date_results sys.odcidatelist := sys.odcidatelist();
  7    count_results sys.odcinumberlist := sys.odcinumberlist();
  8  begin
  9    for i in 1 .. tablist.count
 10    loop
 11      open rc for
 12        replace(q'{select trunc(created,'YYYY'), count(*) from @@@ group by trunc(created,'YYYY') order by 1}', '@@@',tablist(i));
 13      fetch rc bulk collect into date_results, count_results;
 14      close rc;
 15
 16      dbms_output.put_line(tablist(i));
 17      for c in 1 .. date_results.count
 18      loop
 19        dbms_output.put_line(rpad(date_results(c),20)||lpad(count_results(c),20));
 20      end loop;
 21    end loop;
 22  end;
 23  /
ALL_OBJECTS
01-JAN-17                          67892
01-JAN-18                           6228
USER_OBJECTS
01-JAN-18                           1093

PL/SQL procedure successfully completed.
Connor McDonald
  • 10,418
  • 1
  • 11
  • 16
  • Thank You very much...haven't used dynamic ref cursors much...Let me try in my context. :) – Kapil Sep 04 '18 at 04:11
  • 1
    one more small thing, how can I display Time as well...I need value not exactly but similar to `02/SEP/18 12:00:00 AM` – Kapil Sep 04 '18 at 04:42
  • 1
    Done - I used `dbms_output.put_line(to_char(missing_pmm_datetime(c),'DD-MON-YYYY HH24:MI:SS'));` – Kapil Sep 04 '18 at 04:57