OWB - Operating Mode (of a Mapping or Workflow)

About

A mapping and a workflow runs with a operating mode:

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.)

Articles Related

Management

Configuration

Mapping

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

Workflow

A workflow runs with only one operating mode. It takes the maximum value of all operating mode for the mapping.

Metadata

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;

Supports

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;