Skip to main content

Oracle

ODI Load Plan Failure Email Notification with Log File Attached

shutterstock_182199380

This post explains the process to unload an error message from ODI repository log tables SNP_LPI_STEP_LOG, SNP_LPI_EXC_LOG to .txt file and attach it to a Load Plan failure notification email.

Process:

  • Create ‘Error_Notification’ package which uses ODISqlUnload utility to unload error message from log tables and write into text file, OdiSendMail utility to send email to distribution list with error message attached.
  • Create exception in load plan and add ‘Error_Notification’ package scenario.

Steps:

Drag OdiSqlUnload utility from ‘All’ or ‘Files’ folder into package.

1

Enter OdiSqlUnload parameters as below

Oracle - Guide to Oracle Cloud: 5 Steps to Ensure a Successful Move to the Cloud
Guide to Oracle Cloud: 5 Steps to Ensure a Successful Move to the Cloud

Explore key considerations, integrating the cloud with legacy applications and challenges of current cloud implementations.

Get the Guide

What are you most interested in learning today?

AnswerTotal NumberTotal %
What is blockchain380.21
Why blockchain180.1
Industry use cases1060.57
How to get started110.06
Best practices120.06
Total Responses: 185 of 428 (43%)

2

Drag OdiSendMail utility from ‘All’ or ‘Internet’ folder

3

Enter OdiSendMail parameters as below

Where is your organization on its blockchain journey?

AnswerTotal NumberTotal %
Not yet started1300.62
Identifying use cases550.26
Competing a POC90.04
Building a prototype70.03
Doing a pilot50.02
Planning to scale30.01
Total Responses: 209 of 428 (49%)

4

Save package and generate scenario.

Open load plan, go to Exceptions tab on left side, add exception and drag the ‘FAILURE_EMAIL_WITH_LOG_FILE’ scenario as below.

5

Whenever load plan fails, ‘FAILURE_EMAIL_WITH_LOG_FILE’ scenario will be executed and email will be sent to distribution list with error message attached in .txt file.

Thoughts on “ODI Load Plan Failure Email Notification with Log File Attached”

  1. Something is not working at my end. I setup OdiSqlUnload to run the query in the work respository. However, there are no values available for the two substitution APIs:

    SELECT ERROR_MESSAGE
    FROM SNP_LPI_STEP_LOG
    WHERE (I_LP_STEP,I_LP_INST) IN
    (SELECT I_LP_STEP,
    I_LP_INST
    FROM snp_lpi_exc_log
    WHERE
    I_LP_INST =
    NB_RUN = and
    SESS_NO = 579
    )

  2. Ashwin Pittampally Post author

    Substitution API’s for I_LP_INST and NB_RUN will have values when you run this package through Load plan.

  3. Hi Ashwin,

    I have followed the above mentioned steps and unable to execute my exception scenario but when I try to run from Package it is running without any issues and sending the log report to my mail id.

    Why the same scenario present in Load Plan Exception tab is not executing when there is a failure in one of my Load Steps? Did I miss anything here to handle exceptions while configuring ODI.

    Please suggest me with your comments.

    Regards,
    SaiKiran

  4. Ashwin Pittampally Post author

    Hi Sai,

    Select the root step of your load plan and in property inspector -> Exception Handling section set exception behavior to “Run Exception and Raise”.

    Thanks,
    Ashwin.

  5. Hi Ashwin,

    I forgot to set the Exception Step: ‘Exception’ in my Load Plan Root Step. Now I am able to get the notification via Load Plan as well.

    Thanks a lot for quick response. Really, a very good blog so far I visited on ODI.

    Regards,
    SaiKiran

  6. Hi Ashwin,

    I am getting regularly one JDBC error as stated below while executing one of the SDE scenario’s called HREMPLOYEEDIMENSION whenever I go for a Full Load using Load Plan.

    In background, we are using BIApps- HR Analytics OBIA product loading through ELT (ODI)

    Error:
    Caused By: java.lang.RuntimeException: java.lang.IllegalArgumentException: Could not load JDBC driver class [xxx]

    Resolution:
    Currently, we r applying the workaround by redeploying the scenario and starting the Load Plan from beginning. I know this is not a permanent fix or identification of RC. So we would like to know what exactly the issue causing this error.

    To find out this exactly the class name ‘xxx’. I checked in Topology, under “Oracle BI” physical technology contains ‘xxx’ dataserver whose jdbc url, username, pwd is given as ‘xxx’ itself. Please let me know on this if you are aware or need further more information.

  7. Can this be applied in such a way that every time a load plan is regenerated via Config Manager, it will generate the load plan with the exception defined? I tried this by editing the BIAPPS Load Plan–> Load Plan System Components, but no luck. Is it possible?

  8. Hi Ashwin,

    Whenever load plan fails, we used to get mail alerts, But from one week we are not receiving alters to emails and when I open exception step, I see the below error. Could you please help

    8158 Exception jar:file:/C:/oracle/product/11.1.1/Oracle_ODI_1/oracledi/client/jdev/extensions/oracle.odi.navigator.jar!/com/sunopsis/graphical/gif/state/lp_error.png 00:01 03:00:44 03:00:45 ODI-1519 ODI-1519: Serial step “Exception (InternalID:317709852)” failed because child step “X_Send_Mail_Alerts (InternalID:317710852)” is in error.

    Load plan instance 632852, run 1 failed to launch scenario: scenario X_Send_Mail_Alerts does not exist in work repository BIAPPS_WORKREP.

    Regards,
    Dinesh

  9. Hi Ashwin,

    Just came across this while trying to figure out on the mails from ODI. However, I am using ODI 12.2.1.3.0 and when i have the given SQL query in the Odisqlunload its returning the error on the APIs.

    Also could you please share the steps to generate a new log file every time the ETL fails or completes successfully.

    Will this setting will also trigger the mail upon the successful execution of the ETL.

    Regards,
    Sohaib Khan.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Ashwin Pittampally

10 years of experience doing analysis, design, development and implementation of Oracle Business Intelligence applications and ETL solutions using Informatica, DAC, ODI. Worked extensively on Oracle BIAPPS Implementations, SQL querying and Stored Procedures using PL/SQL. Worked on Functional Areas - HR, Finance, Service and Marketing Analytics, Procurement and Spend Analytics, Supply Chain and Order Management Analytics, Enterprise Healthcare Analytics (EHA) and OHADI (Oracle Healthcare Analytics Data Integration)

More from this Author

Categories
Follow Us