oracle-data-integrator

How to prevent duplicates when inserting into target tables with ODI 12c?


I was practicing ODI 12c and I came across this problem:

I have a mapping in ODI 12c that inserts the data from a staging table into a staging history table. The logical diagram is:

source: staging table -> FILTER -> EXPRESSION -> target: staging history table

In the physical tab of the mapping, I set:

EXPRESSION_AP: uses LKM SQL to Oracle (Built In).GLOBAL

staging history table: uses IKM Oracle Control Append for its IKM ( and CKM Oracle for its CKM

Let's say it's May 16th 2025 15:47:42.000, If I run the mapping once, a record will be inserted into the target table with its field DATE_CREATE = 2025-05-16 15:47:42.000

Then I run the mapping again at 15:50:05.000, the table will now contain 2 records, the 2nd one has its field DATE_CREATE = 2025-05-16 15:50:05.000.

These two rows have the same data on every column except the DATE_CREATE, where the only distinction is the hour:minute:second part. So I want to modify the mapping in some way that:

I suspect it has something to do with the IKM I'm using on my staging history table but I don't know where to start and what should I add to the IKM.

A pic of the IKM on my staging history table


Solution

  • I have found a solution: use IKM Oracle Control Append, add an option "Keep latest record" in the Options tab, then add one more task of the same name as the said option before the task Insert new rows in the Execution Unit Main. The task contains this code:

    DELETE FROM <%=odiRef.getTable("L", "TARG_NAME", "A")%>
    WHERE       <%=odiRef.getColList("(", "[COL_NAME]  ", ", ", ")", "UD1")%> = TO_DATE(#test.p_date,'YYYYMMDD')
    

    The code above will generate this sql code:

    DELETE FROM   STG_HIST.NG_BRANCH_HIST
    WHERE         TRUNC((DATE_CREATE)) = TO_DATE( #test.p_date, 'YYYYMMDD') 
    

    When I run the mapping with this customed IKM in the target table, it deletes any record that has its DATE_CREATE matching the date I declare in p_date, before inserting the latest record.
    Note that the DATE_CREATE needs to have its UD1 box checked in the logical tab of the mapping.