title: Oracle: Trying to loop thru insert statement using dynamic list of table names
body: <p>I'm not quite understanding the solution found here:
  <a href="https://stackoverflow.com/questions/1573877/selecting-values-from-oracle-table-variable-array">Selecting Values from Oracle Table Variable / Array?</a></p>
  
  <p>I have a list of table names. I would like to loop thru them as an array, using their values as tables to search from.</p>
  
  <p>TMP_DORMANT_FILTERS physical table of table names. The array below is the same list.
  LM_DORMANT_EMAIL is a list of email addresses.</p>
  
  <p>I want to check the existence of the dormant email addresses in the list of tables. I realize I could write the same query 12 times to search each table. But that's not going to improve my SQL skills. </p>
  
  <p>Here is my array attempt. In this attempt, Oracle doesn't like the way I'm calling the array value in my where not exists sql:</p>
  
  <pre><code>DECLARE 
  TYPE array_t IS 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
  FOR i IN 1..array.count LOOP
  INSERT INTO TMP1_DORMANT_EMAIL
  SELECT feed.EMAIL_ADDRESS
  FROM LM_DORMANT_EMAIL feed
  WHERE NOT EXISTS (
          SELECT 1 FROM array(i) hist
              WHERE ACTIVITY_DATE &gt;= TRUNC(SYSDATE - 90)
              AND hist.EMAIL = feed.EMAIL_ADDRESS
          );
  
  COMMIT;
  
  END LOOP;
  
  END;
  /
  </code></pre>
  
  <p>Or using the solution found at the link above, I tried. Oracle doesn't recognize my inserting into dormant_filters under the begin part. It's telling me my physical table TMP_DORMANT_FILTERS does not exist:</p>
  
  <pre><code>CREATE GLOBAL TEMPORARY TABLE dormant_filters
  ( filters varchar2(100)
  )
  ON COMMIT DELETE ROWS;
  
  BEGIN 
  INSERT INTO dormant_filters
    ( filters )
    ( SELECT TABLE_NAMES 
        FROM TMP_DORMANT_FILTERS 
    ); 
  
   FOR j IN ( SELECT filters FROM dormant_filters ) LOOP 
      INSERT INTO TMP1_DORMANT_EMAIL
          SELECT feed.EMAIL_ADDRESS, j as DORMANT_SOURCE
          FROM LM_DORMANT_EMAIL feed
          WHERE NOT EXISTS (
              SELECT 1 FROM j hist
              WHERE feed.ACTIVITY_DATE &gt;= TRUNC(SYSDATE - 90)
              AND hist.EMAIL = feed.EMAIL_ADDRESS
              );
          NULL;
  END LOOP; 
  COMMIT;
  END; 
  /
  </code></pre>