Cloud

ODI Load Plan Success Email with Execution Log File Attached

shutterstock_69224002

This post explains the process to unload ODI session log information Ex:  Session name, run-time, number of records inserted etc. from SNP_SESSION table to a delimited file and attach it to a Load Plan success notification email.

This attachment can be used to identify the Session’s which are running longer than usual and causing performance issues, identify unusual number of inserts, deletes, updates done by a session.

Process:

  • Create ‘Loadplan_Success_Notification’ package which uses ODISqlUnload utility to unload ODI session log information from SNP_SESSION table and write into Delimited file, OdiSendMail utility to send email to distribution list with Execution log attached.
  • Add ‘Loadplan_Success_Notification’ package at the end of Load plan.

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

Steps:

Create new package ‘Generate Load Plan Success email with Execution Log’ and Drag OdiSqlUnload utility from ‘All’ or ‘Files’ folder into package.

1

Enter OdiSqlUnload parameters as below

Poll Results for What industry are you in?   
Start Time: April 3, 2018 12:13:22 PM MDT

Total Responses: 222 of 428 (52%)

Results Summary
AnswerTotal NumberTotal %
Automotive70.03
Energy and Utilities140.06
Financial Services and Insurance470.21
Healthcare and Life Sciences600.27
Manufacturing140.06
Retail220.1
Other580.26


 

Drag OdiSendMail utility from ‘All’ or ‘Internet’ folder and enter OdiSendMail parameters as below

Poll Results for What industry are you in?   
Start Time: April 3, 2018 12:13:22 PM MDT

Total Responses: 222 of 428 (52%)

Results Summary
AnswerTotal NumberTotal %
Automotive70.03
Energy and Utilities140.06
Financial Services and Insurance470.21
Healthcare and Life Sciences600.27
Manufacturing140.06
Retail220.1
Other580.26


Save package and generate scenario.

Open load plan and add the scenario of package created above as last step in load plan.

Once all steps in Load plan are executed successfully, Package ‘Generate Load Plan Success email with Execution Log’ will be executed and email will be sent to distribution list with execution log attached in delimited file.

About the Author

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

Leave a Reply

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

Subscribe to the Weekly Blog Digest:

Sign Up
Categories