For-Cursor-Loop

DECLARE
  v_aenderungszeitstempel TIMESTAMP(6);
BEGIN
  FOR cur IN (select table_name
                from tabs
               where table_name     like 'CT_%'
                 and table_name not like 'CT_D_%'
                 and table_name not like 'CT_PROT_%') LOOP
    execute immediate 'SELECT max (aenderungszeitstempel) FROM ' || cur.table_name INTO v_aenderungszeitstempel;
    DBMS_OUTPUT.put_line('max Aenderungszeitstempel der Tabelle ' || cur.table_name || ':  ' || v_aenderungszeitstempel);
  END LOOP;
END;

einfache Queries

select table_name
  from tabs
 where table_name     like 'T_%'
   and table_name not like 'T_D_%'
   and table_name not like 'T_PRO_%'
 ;

Dynamisch SQL-Befehle generieren

select 'delete from ' || table_name || ';' from tabs;

select 'insert into ' || table_name || ' select * from ' || table_name || '@dwh;' from tabs;

select 'CREATE TABLE ' || table_name || '_S  AS (SELECT * from  ' || table_name || ');' from tabs;