Using a Variable to capture Error Messages in ODI12c Load Plans

Create a Variable to hold the Error Message. In this example, I am creating a Global Variable.

In the Refreshing tab, enter the following query as shown below:

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 = (SELECT I_LP_INST FROM SNP_LP_INST WHERE GLOBAL_ID = '<%=odiRef.getLoadPlanInstance("BATCH_GUID")%>')
AND NB_RUN      = <%=odiRef.getLoadPlanInstance("RESTART_ATTEMPTS")%>
AND SESS_NO     = <%=odiRef.getSession( "SESS_NO" )%>
)

Notice that ODI Rereference Substitution API's are used in the query.  In this example, I will use the variable in a Load Plan. The error will be retrieved from the Load Plan Execution table's: SNP_LPI_STEP_LOG and SNP_LPI_EXC_LOG. An API: getLoadPlanInstance will be used to specify the id of the running Load Plan. (Another way to query the execution details is through the SNP_SESSION table. )

Save.

Next, create a package to refresh the variable.

Add a Send Mail tool.
In the Message Body Property, specify the variable name.

Now, you can call the package to execute if the Load Plan encounters an error.

References: 

Substitution API Reference

Introduction to OdiRef Substitution API