ODI Pundits - Oracle Data Integrator

The title is pretty self explanatory. In the good old days of 10G when there was no load plans and when  we were dealing with just packages it was quiet easy to get the previous step log using odiRef.getPrevStepLog() function or if you wish to be little fancy you can get use this(http://odiexperts.com/get-the-error-message-of-any-failed-odi-objects-inside-package/) blog post from  ODI Experts

Anyway with load plans we are starting to write the failure notifications in the exception step section. Now the challenge is that there is no substitution api functions or any tools for getting the error message from the failed load plan.

After analyzing the load plan tables in ODI repository I wrote the following query which has to be run in “Exception step” to get the log of the step which raised triggered the exception step.

Lets a Load plan step “Load Geo Dimension” step failed and raised an error and ran the exception step “Send load failed message” to get the error message in “Load Geo Dimension” we have to use the following query in “Send load failed message”

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 = <%=odiRef.getLoadPlanInstance("BATCH_ID")%>
AND NB_RUN      = <%=odiRef.getLoadPlanInstance("RESTART_ATTEMPTS")%>
AND SESS_NO     = <%=odiRef.getSession( "SESS_NO" )%>
)

 

Since error message is a CLOB, my recommendation is to use OdiSqlUnload tool to dump the error message in a file and attach the file to the email notification (If you are using an email).

Note: Very Less has been written about the structure of the load plan tables as compared to session tables (SNP_SESS). Future blog posts will try to detail the structure of the load plan tables.