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.
Enter OdiSqlUnload parameters as below
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.
What are you most interested in learning today?
Answer Total Number Total %
What is blockchain 38 0.21
Why blockchain 18 0.1
Industry use cases 106 0.57
How to get started 11 0.06
Best practices 12 0.06
Total Responses: 185 of 428 (43%)
Drag OdiSendMail utility from ‘All’ or ‘Internet’ folder
Enter OdiSendMail parameters as below
Where is your organization on its blockchain journey?
Answer Total Number Total %
Not yet started 130 0.62
Identifying use cases 55 0.26
Competing a POC 9 0.04
Building a prototype 7 0.03
Doing a pilot 5 0.02
Planning to scale 3 0.01
Total Responses: 209 of 428 (49%)
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.
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.
Just came across your post searching for some else on ODI. Thank you very much. Very useful.
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
)
Thank you Jules..
Substitution API’s for I_LP_INST and NB_RUN will have values when you run this package through Load plan.
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
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.
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
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.
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?
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
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.