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

Question

I'm not quite understanding the solution found here: Selecting Values from Oracle Table Variable / Array?

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.

TMP_DORMANT_FILTERS physical table of table names. The array below is the same list. LM_DORMANT_EMAIL is a list of email addresses.

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.

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:

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 >= TRUNC(SYSDATE - 90)
            AND hist.EMAIL = feed.EMAIL_ADDRESS
        );

COMMIT;

END LOOP;

END;
/

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:

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 >= TRUNC(SYSDATE - 90)
            AND hist.EMAIL = feed.EMAIL_ADDRESS
            );
        NULL;
END LOOP; 
COMMIT;
END; 
/

Jon Heller · Accepted Answer · 2014-11-16T22:29:04.433

This problem requires dynamic SQL. Bind variables can be used for values but not for objects.

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
        execute immediate '
            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 >= TRUNC(SYSDATE - 90)
                        AND hist.EMAIL = feed.EMAIL_ADDRESS
                    )
        ';
        commit;
    end loop;
end;
/

UPDATE

If the column names are different for each table you can use the data dictionary to pick the correct column name.

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');
    v_column_name varchar2(30);
begin
    for i in 1 .. array.count loop
        select column_name
        into v_column_name
        from all_tab_columns
        where owner = 'SCHEMA NAME'
            and table_name = array(i)
            and column_name in ('ACTIVITY_TIME','DATE_ABANDONED');

        execute immediate '
            INSERT INTO TMP1_DORMANT_EMAIL
            SELECT feed.EMAIL_ADDRESS
            FROM LM_DORMANT_EMAIL feed
            WHERE NOT EXISTS (
                    SELECT 1 FROM '||array(i)||' hist
                        WHERE '||v_column_name||' >= TRUNC(SYSDATE - 90)
                        AND hist.EMAIL = feed.EMAIL_ADDRESS
                    )
        ';
        commit;
    end loop;
end;
/

Thank you Jon for this solution. It is working. There is one more minor thing. The date columns in a couple of the tables is named differently than the rest. To avoid writing two sql blocks, I tried the following, but it doesn't work... WHERE DECODE('||k||',1,ACTIVITY_TIME,2,ACTIVITY_TIME,DATE_ABANDONED) >= TRUNC(SYSDATE - 90) The decode statement doesn't seem to work. Oracle tells me that DATE_ABANDONED is invalid identifier. But the first two tables in my array use ACTIVITY_DATE and the rest use DATE_ABANDONED. — tmccoy, Nov 16 '14 at 15:28
My sql is correct, the column names are ACTIVITY_TIME, not ACTIVITY_DATE like I posted prior. — tmccoy, Nov 16 '14 at 15:38

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

1 Answers1

Linked