The blog for Oracle Data Integrator ( ODI )

11g Oracle Data Integrator – Part 6/11g – Union , Minus, Intersect

Hello Friends ,

We have shows you few important features of the new ODI 11g ,today we are going to show you how to implement Union , Minus etc logic in ODI 11g which was lacking in older version and thus maturing ODI in the ETL world.

After the initial step of the Interface name ,Click on the Add/Remove Dataset


Rename the Dataset Name to Source 1, Source 2 for more meaningful or else you can provide the Table name too and in the Second Dataset name select the Operator as Union.


Drag in the Source tables and Target table and map the columns accordingly.


Go the Flow tab and select the Staging Area and select the appropriate LKM and IKM.

[ since my Source are in the Same database so no LKM ) and Execute


ODI 11g provide a new feature called Simulation as that generates all the required codes without running the Interface , useful functionality when you trying carry some complete logic where you want to test in SQL Developer or Toad because carrying out in ODI .

It worked and we got 107 inserts


Lets see the codes and as we see it has carried out the required UNION operation between two tables.


Let’s try the same interface for Minus with truncate table option yes in Target.

To do so, we have just changed the operator for Source 2 to Minus and execute



Similarly it goes for UNION_ALL and INTERSECT

What if we want to do UNION between three tables? Just select the right operator and the ODI will create the logic as defined.



This functionality give an extra edge for ODI on ETL side.

Keep looking odiexperts for more ODI 11g  functionalities .


Leave a Reply

Required fields are marked *.

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