create or replace PROCEDURE Pro_Drivemail_rebound
Is
V_SQL VARCHAR2(5000);
--CNT NUMBER;
Begin
EXECUTE IMMEDIATE 'TRUNCATE TABLE DRIVEMAIL_REBOUND_TMP';
COMMIT;
------------------------------------------------------------------------------------------
FOR C IN (SELECT * FROM TAB WHERE TNAME LIKE 'TASK_EMAILS_%' AND LENGTH(SUBSTR(TNAME,INSTR(TNAME,'_',1,2)+1))>=6) LOOP
V_SQL:='INSERT /*+ APPEND */ INTO DRIVEMAIL_REBOUND_TMP NOLOGGING
(ID,EMAIL,SENDING_TIME)
SELECT ID,EMAIL,SENDING_TIME
FROM '||C.TNAME||' WHERE ACTIVE<-50 AND ACTIVE>-500';
--dbms_output.put_line(v_sql);
EXECUTE IMMEDIATE V_SQL;
COMMIT;
END LOOP;
------------------------------------------------------------------------------------------
INSERT /*+ APPEND */ INTO DRIVEMAIL_REBOUND Nologging
SELECT *
FROM DRIVEMAIL_REBOUND_TMP A
WHERE NOT EXISTS (SELECT 1 FROM DRIVEMAIL_REBOUND B WHERE A.EMAIL = B.EMAIL);
COMMIT;
------------------------------------------------------------------------------------------
EXECUTE IMMEDIATE 'TRUNCATE TABLE DRIVEMAIL_REBOUND_TMP';
COMMIT;
End Pro_Drivemail_rebound;