Oracle / OWB: Specifying the partition for INSERT at runtime

Question

(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?

Solution

The PARTITION FOR 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. 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 system partitioning and DATAOBJ_TO_PARTITION .

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 SYSTEM
(
  PARTITION ess1,
  PARTITION ess2
);

declare
    v_object_id number;
begin
    select object_id
    into v_object_id
    from dba_objects
    where object_name = 'TMP_LOADING_TABLE'
        and subobject_name = 'ESS1';

    insert into tmp_loading_table
    partition (dataobj_to_partition (tmp_loading_table, v_object_id))
    values (1, 2, 'A');
end;
/

The huge disadvantage of this method is that every DML must reference the partition:

insert into tmp_loading_table
values (1, 2, 'A');

ORA-14701: partition-extended name or bind variable must be used for DMLs on tables partitioned by the System method

I've never heard of anyone using this feature. And in my experience Oracle data cartridge is buggy. How's that dynamic SQL looking now? :)