Sometimes, ETL packages doesn't care about errors as errors can be handled after ETL is finished, but we want to know about errors and fix them. I use this procedure to mail errors to ETL group.

Sometimes, ETL packages doesn't care about errors as errors can be handled after ETL is finished, but we want to know about errors and fix them. I use this procedure to mail errors to ETL group.

CREATE OR REPLACE procedure STG.ODI_SESSION_ERROR_LOG ( p_etl_date number ) is

begin
DELETE FROM ODI_SESSION_ERROR_LOG T2
WHERE 1=1
         and TRUNC (t2.ETL_DATE ) >=to_date(to_char(p_etl_date), 'YYYYMMDD');
       
         COMMIT;

INSERT INTO ODI_SESSION_ERROR_LOG
 SELECT t1.SESS_NO
        ,t1.SCEN_TASK_NO
        ,t1.TASK_NAME2
        ,t1.TASK_NAME3
        ,t2.ERROR_MESSAGE
        ,t2.TASK_STATUS
        ,t2.task_beg  ETL_DATE
        , sysdate INSERT_DATE
    FROM snp_sess_task t1, SNP_SESS_TASK_LOG t2
   WHERE     1 = 1
         AND t1.sess_no = t2.sess_no
         AND T1.SCEN_TASK_NO = T2.SCEN_TASK_NO
         and TRUNC (t2.TASK_BEG ) >=to_date(to_char(p_etl_date), 'YYYYMMDD')
         AND TASK_STATUS ='E'
         and exists (select t3.sess_no from  snp_session t3
         where 1=1
         and t1.sess_no=t3.sess_no
         CONNECT BY PRIOR t3.sess_no = t3.parent_sess_no
      START WITH t3.sess_name = 'PCK_ETL'--Package of ETL in ODI
         )
ORDER BY 1, 2;

COMMIT;

end;
/





exec STG .ODI_SESSION_ERROR_LOG(to_char(trunc(sysdate), 'YYYYMMDD'));




You can get load plan execution errors with the code below:


SELECT * FROM ODIW.SNP_LP_INST a, ODIW.SNP_LPI_RUN b
WHERE a.I_LP_INST=b.I_LP_INST
and b.STATUS='E'
and LOAD_PLAN_NAME ='<load_plan_name>'