The blog for Oracle Data Integrator ( ODI )


The below post shows the  an easy method to create a SCD TYPE 3 functionality.

Step 1 . Create a copy of the IKM Oracle Incremental Update and rename to IKM Oracle Incremental SCD Type 3

Step 2 . Add the following codes as a command and name it ” Historical Update Rows ” and scroll the level to before  ” Update existing Rows ”

UPDATE    <%=odiRef.getTable("L", "TARG_NAME", "A")%> T
set <%=odiRef.getColList("", "T.[COL_NAME]", ",nt", "", "(UD2)")%> =
<%=odiRef.getColList("", "T.[COL_NAME]", ",nt", "", "(UD1)")%>
where    (<%=odiRef.getColList("","T.[COL_NAME]", ", ", "", "UK")%>)
    in    (
select    <%=odiRef.getColList("","T.[COL_NAME]", ",nttt", "", "UK")%>
from     <%=odiRef.getTable("L", "TARG_NAME", "A")%> T ,
<%=odiRef.getTable("L", "INT_NAME", "A")%> S
where   <%=odiRef.getColList("","T.[COL_NAME]", ", ", "", "UK")%> =
<%=odiRef.getColList("","S.[COL_NAME]", ", ", "", "UK")%> AND
<%=odiRef.getColList("", "T.[COL_NAME]", ",nt", "", "(UD1)")%>!=
<%=odiRef.getColList("", "S.[COL_NAME]", ",nt", "", "(UD1)")%>


Step 3.  Go to your interface and assign UD1 to  Column having current Value  say for ex Salary and UD2 to column to be maintained for historical value  say for ex Previous Salary (Uncheck Insert and Update and check only UD2)

Step 4 . Lets take a look at an example for a complete overview.

Here i have two columns Salary and Previous Salary . I have marked the Salary as UD1  and previous_salary as UD2(uncheck Insert and Update and check only UD2)




I am updating the salary to 8210 and 7710 respectively at the source side .

Step 5 . Save and run the interface .

Let me change salary data for two records and lets see if it comes through.


It does. Have fun and look for odiexperts for more fun and interesting ideas.


Leave a Reply

Required fields are marked *.

This site uses Akismet to reduce spam. Learn how your comment data is processed.