Control Flow in a Map
In a multi step mapping here is an approach to determine the status of steps within the map and control processing.
If you have 2 steps in a map step A and B, after each step in your map you can add another step which checks the
status of the previous step and based on a
condition does what you need. This concept is based on a set based (pure SQL map) and also the dependency on step result status is based on the OWB runtime audit trail, so if you do not use the audit trail, this will not fly.
The OWB runtime audit views such as ALL_RT_AUDIT_STEP_RUNS can be used. You can either reverse engineer these into your project and use them in the map or add an unbound table/view operator such as ALL_RT_AUDIT_STEP_RUNS into the map.
Using this view if you add a filter which does something like;
AND INOUTGRP1.STEP_NAME ='SALES_STEPA'
AND INOUTGRP1.NUMBER_RECORDS_INSERTED > 0
where the
previous step has a target operator named SALES_STEPA (which is bound to
the SALES table for example), the filter will check the current map execution (since get_runtime_audit_id is used), step
SALES_STEPA and see if any records were inserted for example then you
can put whatever logic you need after the filter. You would add this
logic after each step in the map. The filter can do whatever you need, check errors, records inserted, updated etc.
To illustrate, here is simple map with 2 main data steps (2 data targets we are really interested in) named SALES_STEPA and SALES_STEPB. I've added intermediate steps for saving some state information in MY_CONTROL_TABLE after each step.
I have used the map's target load order to ensure that the order is SALES_STEPA, MY_CONTROL_TABLE, SALES_STEPB, MY_CONTROL_TABLE2. The runtime view ALL_RT_AUDIT_STEP_RUNS has a number of useful columns for determining the status of the step and what happened.
Above we've looked at the body of the map. There are also pre and post map customization points where you can call a PLSQL function or procedure. For pre map customization use the 'Pre-Mapping Process'. With this operator based upon its result the rest of the mapping can be conditionally executed. Use the property 'Mapping Run Condition' to determine whether the body of the map should be executed, by default this property has value SUCCESS, so only if the routine completes successfully will the body be executed. For post map customization use the 'Post-Mapping Process' operator. With this operator it can be conditionally executed based upon the result of the map body. The property 'Post-Mapping Process Run Condition' is defaulted to ON SUCCESS, but this can be changed to ALWAYS / ON ERROR / ON WARNING if desired.
Hopefully this overview of the anatomy of a map is a useful starter.
