25 Source and Target Operators. Oracle Warehouse Builder determines a. When you set the loading type of a target to INSERT/UPDATE or UPDATE/INSERT in.

(Background: we're running a data warehouse built with Oracle Warehouse Builder. Recently, we started getting a lot of 'ORA-02049: distributed transaction timeout while waiting for lock' errors. The reason for this is that we're running several ETL jobs in parallel, and each of these jobs does a INSERT /*+APPEND PARALLEL*/ into our staging table.

This staging table is partitioned by the source system ID. ) I'd like to know if it is possible to specify the partition key for an INSERT at runtime.

Suppose I have a table create table tmp_loading_table ( etl_source_system_fk number not null enable, object_id number not null enable, object_name varchar2(30) not null enable ) PARTITION BY LIST ('ETL_SOURCE_SYSTEM_FK') (PARTITION 'ESS1' VALUES (1), PARTITION 'ESS2' VALUES (2) ); then I can insert into a specific partition using insert /*+ APPEND PARALLEL('TMP_LOADING_TABLE') */ into tmp_loading_table partition(ESS1) ( etl_source_system_fk, object_id, object_name) (select 1 etl_source_system_fk, object_id, object_name from user_objects); but this requires me to hard-code the partition name. Since our OWB mappings are generic (they get the source system ID as a parameter), I'd like to provide the partition name at runtime, something like insert /*+ APPEND PARALLEL('TMP_LOADING_TABLE') */ into tmp_loading_table partition(:partition_name) ( etl_source_system_fk, object_id, object_name) (select 1 etl_source_system_fk, object_id, object_name from user_objects); Is this possible? If not, is there another method to achieve this with Oracle Warehouse Builder? The syntax and dynamic SQL can help. Ideally it would be this simple: declare v_partition_value number:= 1; begin insert /*+ APPEND PARALLEL('TMP_LOADING_TABLE') */ into tmp_loading_table partition for (v_partition_value) ( etl_source_system_fk, object_id, object_name) (select 1 etl_source_system_fk, object_id, object_name from user_objects); end; / Unfortunately the above code fails with ORA-14108: illegal partition-extended table name syntax. Which is strange since that seems like an obvious use for that syntax.

Adding dynamic SQL removes the error. Declare v_partition_value number:= 1; begin execute immediate ' insert /*+ APPEND PARALLEL('TMP_LOADING_TABLE') */ into tmp_loading_table partition for (' v_partition_value ') ( etl_source_system_fk, object_id, object_name) (select 1 etl_source_system_fk, object_id, object_name from user_objects)'; end; / I'm not familiar with Oracle Warehouse Builder and do not know if this solution will work in that environment. Windows Vista Oa Em Latam here. And I assume that in a data warehouse SQL injection is not a concern. Another way to specify the partition name at runtime is with and.