- Set-based (SQL)
- Row-based (PL/SQL)
- Set-based Fail Over to Row-based (If Sql failed, go to PL/SQL)
- Set-based Fail Over to Row-based Target Only (If Sql failed, go to PL/SQL only for the DML operations on the target table.)
Their is two parameters that control this mode:
- Code generation options: Generation Mode: Will the code for SQL and the code for PL/SQL generated ?
- Runtime parameters: Default Operating Mode. This parameter will be taken when the mapping is executed and when the mapping is added to a workflow. See below
A workflow runs with only one operating mode. It takes the maximum value of all operating mode for the mapping.
You can see the operating mode that was used during the run of a mapping through the public views
select rt.execution_audit_id, rt.created_on,rt.object_name, rt.return_result, step.step_type, rt.return_code, rt.return_result_number, mess.message_text from ALL_RT_AUDIT_EXECUTIONS rt left join (select max(message_text) as message_text, execution_audit_id from all_rt_audit_exec_messages mess group by execution_audit_id) mess on rt.execution_audit_id = mess.execution_audit_id inner join ALL_RT_AUDIT_MAP_RUNS runs on rt.execution_audit_id = runs.execution_audit_id left join all_rt_audit_step_runs step on runs.map_run_id = step.map_run_id where rt.created_on > sysdate - (3/24) and rt.object_name = 'MAPPING_NAME' order by rt.created_on desc;
ORA-20101: Row based mode not supported
ORA-20101: Row based mode not supported ORA-06512: at "WN_DW.M_OBI_GROEP_GEBRUIKER", line 854 ORA-06512: at line 1
In a workflow:
- if a mapping is not generated with the Row based mode
- another mapping within this workflow has a Row-based mode (wit failure or not)
the workflow will send the above error.
Resolution: change the code generation option of the mapping from Set based to include the row based mode (for instance: All operating modes).
You can see this check in the PL/SQL body package generated:
IF get_operating_mode = MODE_ROW OR get_operating_mode = MODE_SET_FAILOVER_ROW THEN RAISE_APPLICATION_ERROR(-20101, 'Row based mode not supported'); END IF;