Oracle: Trying to loop thru insert statement using dynamic list of table names -

i'm not quite understanding solution found here: selecting values oracle table variable / array?

i have list of table names. loop thru them array, using values tables search from.

tmp_dormant_filters physical table of table names. array below same list. lm_dormant_email list of email addresses.

i want check existence of dormant email addresses in list of tables. realize write same query 12 times search each table. that's not going improve sql skills.

here array attempt. in attempt, oracle doesn't way i'm calling array value in not exists sql:

declare  type array_t varray(12) of varchar2(25);  array array_t := array_t('bt_abandoned_hist', 'bt_browsed_hist', 'bt_purchased_hist',       'cm_abandoned_hist', 'cm_browsed_hist', 'cm_purchased_hist', 'cm_page_views_hist', 'mb_abandoned_hist', 'mb_browsed_hist', 'mb_carted_hist', 'mb_page_views_hist', 'mb_purchased_hist');  begin in 1..array.count loop insert tmp1_dormant_email select feed.email_address lm_dormant_email feed not exists (         select 1 array(i) hist             activity_date >= trunc(sysdate - 90)             , hist.email = feed.email_address         );  commit;  end loop;  end; / 

or using solution found @ link above, tried. oracle doesn't recognize inserting dormant_filters under begin part. it's telling me physical table tmp_dormant_filters not exist:

create global temporary table dormant_filters ( filters varchar2(100) ) on commit delete rows;  begin  insert dormant_filters   ( filters )   ( select table_names        tmp_dormant_filters    );    j in ( select filters dormant_filters ) loop      insert tmp1_dormant_email         select feed.email_address, j dormant_source         lm_dormant_email feed         not exists (             select 1 j hist             feed.activity_date >= trunc(sysdate - 90)             , hist.email = feed.email_address             );         null; end loop;  commit; end;  / 

this problem requires dynamic sql. bind variables can used values not objects.

declare      type array_t varray(12) of varchar2(25);     array array_t := array_t('bt_abandoned_hist', 'bt_browsed_hist', 'bt_purchased_hist', 'cm_abandoned_hist', 'cm_browsed_hist', 'cm_purchased_hist', 'cm_page_views_hist', 'mb_abandoned_hist', 'mb_browsed_hist', 'mb_carted_hist', 'mb_page_views_hist', 'mb_purchased_hist'); begin     in 1 .. array.count loop         execute immediate '             insert tmp1_dormant_email             select feed.email_address             lm_dormant_email feed             not exists (                     select 1 '||array(i)||' hist                         activity_date >= trunc(sysdate - 90)                         , hist.email = feed.email_address                     )         ';         commit;     end loop; end; / 

update

if column names different each table can use data dictionary pick correct column name.

declare      type array_t varray(12) of varchar2(25);     array array_t := array_t('bt_abandoned_hist', 'bt_browsed_hist', 'bt_purchased_hist', 'cm_abandoned_hist', 'cm_browsed_hist', 'cm_purchased_hist', 'cm_page_views_hist', 'mb_abandoned_hist', 'mb_browsed_hist', 'mb_carted_hist', 'mb_page_views_hist', 'mb_purchased_hist');     v_column_name varchar2(30); begin     in 1 .. array.count loop         select column_name         v_column_name         all_tab_columns         owner = 'schema name'             , table_name = array(i)             , column_name in ('activity_time','date_abandoned');          execute immediate '             insert tmp1_dormant_email             select feed.email_address             lm_dormant_email feed             not exists (                     select 1 '||array(i)||' hist                         '||v_column_name||' >= trunc(sysdate - 90)                         , hist.email = feed.email_address                     )         ';         commit;     end loop; end; /