title: Oracle / OWB: Specifying the partition for INSERT at runtime
body: <p>(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 <code>INSERT /*+APPEND PARALLEL*/</code> into our staging table. This staging table is partitioned by the source system ID. )</p>
  
  <p>I'd like to know if it is possible to specify the partition key for an INSERT at runtime.
  Suppose I have a table</p>
  
  <pre><code>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)  
  );
  </code></pre>
  
  <p>then I can insert into a specific partition using</p>
  
  <pre><code>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);
  </code></pre>
  
  <p>but this requires me to hard-code the partition name. </p>
  
  <p>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</p>
  
  <pre><code>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);
  </code></pre>
  
  <p>Is this possible? If not, is there another method to achieve this with Oracle Warehouse Builder?</p>