Get ODI Load Plan step name dynamically using ODI Substitution methods

ODI 11g provided the Load plan functionality to the users, along with its substitution methods to create generic objects in ODI.

Case: User requirement is to refresh multiple Materialized views from ODI Load plans.

1. Create ODI procedure to refresh the materialized view.

begin

dbms_snapshot.refresh('SCHEMA_NAME.MVIEW_NAME',ATOMIC_REFRESH=>FALSE);

end;

If we have hundreds of JOBs to execute, then we have to create those numbers of ODI Procedures. And same needs to be add into the load plans, which would generate extra overhead of JOB integrations in ODI.

Instead of that we would recommend to create a one generic/reusable ODI procedure and use that across the load plans, where you need to set the load plan step names with the help of <%=odiRef.getLoadPlanStepInstance("STEP_NAME")%> substitution method.

Add below code into the ODI Procedure.

begin

dbms_snapshot.refresh('SCHEMA_NAME. <%=odiRef.getLoadPlanStepInstance("STEP_NAME")%>’

,ATOMIC_REFRESH=>FALSE);

end;

2. Add the ODI Procedure into load plan and change the name of load plan steps according to the JOBs. The provided step name would get captured into that ODI procedure code at run-time.

3. Execute the load plan and see the logs for those executed jobs. Check the code of executed procedure where we can see the JOB name which we have provided into the load plan.

Cheers..!! :)