Transaction Control Order on Oracle DB

Hi,

Does "Including in Transaction Flag" works, if you have same target tables, i.e., In one you do all Inserts, then Updates and finally Deletes operation.

I have this file with following format, basically it is transactional dummy data.

ID,OPCODE,NAME,STATE

1,I,SAM,OH

2,I,RAM,NJ

3,I,TOM,WI

4,I,LIL,IN

5,I,ABY,NY

6,U,TOM,CA

7,D,ABY,NY

8,I,PAM,VA

9,U,ABY,DC

I am trying to load this data in Target table say A. Here for OPCODE = "I", I have to generate primary key, while for OPCODE = "U" and "D", I have to either Delete for Update the Target based upon Natural Key, which in this case is column "NAME".

So I have used, a CASE transform after the Source, and I am branching out on OPCODE column.

Code:

First Branch ==> Key gen ==> Target A {Inserts Row here}

Second branch ==> Map Update ==> Target A {Perform Updates, if any}

Third branch ==> Map Delete ==> Target A {Perform Delete, if any}

Target A, same table dragged thrice, in the data flow!

For Second and Third branch, I have made "NAME" as my primary key, and have enabled "USE INPUT KEY" option at the TARGET table, for Update and Delete, and the flag "commit at end of insert...select" is checked for all the Target A table Instances!

I have also enabled "Include in Transaction" using following logic

Code:

First Branch ==> Key gen ==> Target A ==> Transaction Order : 1

Second branch ==> Map Update ==> Target A ==> Transaction Order : 2

Third branch ==> Map Delete ==> Target A ==> Transaction Order : 3

This is the correct output that it should generate:

ID,NAME,STATE

1,SAM,OH

2,RAM,NJ

6,TOM,CA

4,LIL,IN

8,PAM,VA

It only performs INSERTS operations, no Updates or Deletes. Is it because, I have same target table thrice, so even by enabling "Transaction Order" flag it doesn't really matter.

Or is it "Transaction Order" Flag works only if you have different Target table but from Same data store?

Technically, it should load all the data for Transaction Order : 1 first, then Transaction Order : 2 and finally Transaction Order : 3, right ?.

I am using Oracle 10g DB, with 11.7.3 on Win server 2003.

Is this a bug, or this logic can't be implemented in DI?

Any help will be appreciated!

Thanks,