Automating Load Plan to check previous Load status in ODI

The most common problem with ODI incremental loads: whenever a prior incremental load fails, we have to manually inactivate the load plan schedule and update the agent schedule to stop executing the next load. Once the prior incremental load’s issue is fixed and its execution completed successfully, we have to activate the load plan schedule and update the agent schedule. We can avoid this manual intervention by adding logic to Load plan to check for prior run status.

This post will explain steps to add load plan a check for prior run status.

In this approach we will do changes to ‘EBS_DW_LOAD’ load plan to check previous incremental load status.

• Create new table WC_LOADPLAN_STATUS in target database schema, this table will store load plan name and last execution date. Load Plan record will be deleted at the beginning of load plan and reinserted on successful completion of load plan. If load plan is failed or stopped in between this table will not have a load plan record.

• CREATE TABLE WC_LOADPLAN_STATUS (
  LOADPLAN_NAME VARCHAR2 (255 BYTE) NOT NULL ENABLE
  , LAST_EXECUTION_DATE DATE
  );

• Create new procedure ‘Delete_EBS_DW_LOAD_FROM_WC_LOADPLAN_STATUS’ using below query to delete ‘EBS_DW_LOAD’ record from WC_LOADPLAN_STATUS. This procedure is executed at the beginning of load plan.

• DELETE
  FROM WC_LOADPLAN_STATUS
  WHERE LOADPLAN_NAME = ‘EBS_DW_LOAD’;

• Create new procedure ‘INSERT_ EBS_DW_LOAD _INTO_WC_LOADPLAN_STATUS’ using below query to insert ‘EBS_DW_LOAD’ record into WC_LOADPLAN_STATUS. This procedure is executed at the end of load plan.

• INSERT INTO WC_LOADPLAN_STATUS
  (LOADPLAN_NAME, LAST_EXECUTION_DATE)
  SELECT ‘EBS_DW_LOAD’, SYSDATE FROM DUAL;

• Create new variable EBS_DW_LOAD_STATUS, this variable uses below query and stores value ‘0’ when previous incremental is completed else it stores ‘1’.

• SELECT CASE (SELECT LOADPLAN_NAME
  FROM WC_LOADPLAN_STATUS
  WHERE LOADPLAN_NAME = ‘EBS_DW_LOAD’)
  WHEN ‘EBS_DW_LOAD’
  THEN 0
  ELSE 1
  END
  FROM dual

•  Create new package ‘Raise Previous Incremental Load Failed Exception’, this package will raise error with message ‘Previous Incremental Load Failed or not completed’, please refer to this POST for creating this package. 

Load plan changes

1. EBS_DW_LOAD_STATUS variable is refreshed, this variable will have value ‘0’ if previous load completed else ‘1’.
2. Case statement evaluates the value in EBS_DW_LOAD_STATUS variable, select refresh variable for this step in property inspector.
3. When value is ‘0’, executes procedure ‘Delete_ EBS_DW_LOAD_STATUS_FROM_WC_LOADPLAN_STATUS’ and exits the case statement, continues with the load plan execution.
4. When value is ‘1’, executes scenario ‘Raise Previous Incremental Load Failed Exception’, this scenario raises exception and alert email is sent to distribution list.
5. Once all load plan scenarios are completed, procedure ‘INSERT_ EBS_DW_LOAD_INTO_WC_LOADPLAN_STATUS’ is executed.

If you want to start next incremental load with aborting the prior load, execute the procedure ‘INSERT_ EBS_DW_LOAD_INTO_WC_LOADPLAN_STATUS’. This procedure inserts load plan record into WC_LOADPLAN_STATUS, so next load runs successfully without raising error.

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

Execute the load plan to test

When prior incremental failed, load plan fails with error “CustomException: >>>>>>> ‘Previous Incremental Load Failed or not completed’ <<<<<<<<” as expected

Load plan execution looks like below, it fails before starting load

When prior incremental is completed successfully, load plan execution looks like below

Conclusion:

By adding this logic, next incremental load will not start until earlier incremental load is completed. Manual intervention can be avoided to inactivate and activate Load plans schedule. With this logic in place Load Plan cannot be submitted more than once simultaneously