Best Practices from Oracle Development's A‑Team

Exceptions Handling and Notifications in ODI

Christophe Dupupet
Sr Director, A-Team - Cloud Solutions Architects


Content validated on 5/6/2021 with
  • ODI Version
  • Oracle DB System


ODI processes are typically at the mercy of the entire IT infrastructure’s health. If source or target systems become unavailable, if network incidents occur, ODI processes will be impacted. When this is the case, it is important to make sure that ODI can notify the proper individuals. This post will review the techniques that are available in ODI to guarantee that such notifications occur reliably.

Exceptions Handling and Notifications in ODI

When you create an ODI package, one of the ODI Tools available to you for notifications is OdiSendMail:


Figure 1: OdiSendMail

Specify the SMTP server, to and from parameters, subject and message for your email and you can alert operators, administrators or developers when errors occur in your packages.

One challenge though is that if you want an email to be sent for any possible error in your package, each and every step of the package must be linked to this tool in case of error. This can get to be quite overwhelming, and there is no guarantee that one step will not be forgotten by a developer along the way.

Error Processing in Packages

Figure 2: Sending an email for every step that ends-up in error

Another caveat with sending a notification email from the package itself is that the final status of the package will be successful as long as sending the email is successful. If you want the package to fail, you now have to raise an exception after sending the email to force the package to end in an error state.

If we look back at the original objective, all we really want is to send a notification no matter what error occurs. So ideally we want the package to actually fail. Only after it has failed should we send the notification email. This is exactly what we can perform with the Load Plan Exceptions.

Load Plans and Exceptions

If we handle the exceptions at the load plan level there is no need to send the notification emails from the original package. What must do now is to create two separate packages: one to process the data, another one to handle the exceptions.

Package without notifications

Figure 3: package without notification emails


Notification Package

Figure 4: package dedicated to notification emails

There are two main aspects to a load plan:

  • Steps: a set of scenarios that must be orchestrated, whether executions are serialized of parallelized

  • Exceptions: a series of scenarios that can be used in case one of the steps fails to execute properly.

LP Steps

Figure 5: Load plans steps

For instance in Figure 6 below, we have created an exception called Email Notification. This exception will run a dedicated scenario whose sole purpose is to send a notification email. The scenario here is generated from the package represented earlier in figure 4.


Exception Scenario

Figure 6: Load Plans Exception

When you edit the properties of the steps of the load plans, you can choose whether they will trigger the execution of an exception scenario or not. For each step, including the root step, you can define what the behavior will be in case of error:

  • Choose the exception scenario to run (or leave blank)

  • Run the exception scenario (if one is selected) and stop the execution of the load plan at this point

  • Run the exception scenario (if one is selected) and proceed with the rest of the load plan.


Select Exceptions for Steps

Figure 7: selection of an exception scenario

If you select an exception at the root level, this will be the default exception for the entire load plan unless you decide to overwrite this default with a different selection in the properties of individual branches or step of the load plan.

This will guarantee that no matter what fails in the scenarios used in the load plan, the notification email is always sent. This includes corner cases where the scenarios would not even start, for instance in case of errors in Topology definitions such as missing physical schema definitions.

In figure 8 below we can see steps of the load plan where the scenario that was executed fails, raising an exception:


Failed Execution with exception handling notification

Figure 8: scenario step raising an exception

We can also look at the scenario sessions to see the original scenario failure and the execution of the notification scenario:

Sessions view of executions

Figure 9: scenario sessions for failed scenario and notification scenario.


Leveraging exceptions handling in Load Plans is a very straightforward and efficient way to ensure that no matter what fails in your ODI processes, notifications are sent reliably and consistently.

For more ODI best practices, tips, tricks, and guidance that the A-Team members gain from real-world experiences working with customers and partners, visit Oracle A-Team Chronicles for ODI.

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.Captcha