OWB - Operating Mode (of a Mapping or Workflow)

> Data Integration Tool (ETL/ELT) > Oracle Warehouse Builder

1 - About

A mapping and a workflow runs with a operating mode:

  1. Set-based (SQL)
  2. Row-based (PL/SQL)
  3. Set-based Fail Over to Row-based (If Sql failed, go to PL/SQL)
  4. 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.)

3 - Management

3.1 - Configuration

3.1.1 - 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
Advertising

3.1.2 - Workflow

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

3.2 - 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;

4 - Supports

4.1 - 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;
Advertising