This content has been marked as final. Show 2 replies
Have you considered the possibility of using dynamic SQL? That way you could specify the query once and either build it prior to running or run multiple times changing the table name each time.
Edited by: Mike Parr on 24-Jan-2013 08:03
I guess the missing piece of your puzzle is the oracle data dictionnary :)
This should help you print out your result then take out the last line's union all and replace it by a semi-colon *;* and execute it :).
select 'SELECT count(class) from '||a.owner||'.'||a.table_name||'WHERE class=9 and RECTIME BETWEEN TO_DATE('''||'01.01.0001' ||''',''DD.MM.YYYY'') AND TO_DATE('''||'01.01.0002' ||''',''DD.MM.YYYY'')union all' from dba_all_tables a where a.table_name like 'YOURFILTER%' and a.owner = 'YOUR OPTIONNAL OWNER';
This should do the trick only using SQL engine. Although I definitly agree with Mike, PL/SQL particularly with DBMS_SQL package can tremendously help automate theses actions :)
Hope this is accurate and/or helps a bit !