Hi Gurus,
Let us see the steps of ‘How to Load source Data table EMP with condition Commission is NOT NULL into target table TRG_EMP_FIL3 through Oracle Data Integrator(ODI).
Note: To view any image clearly just double click on it
Requirements:
· Oracle Database 11g
· SQL Developer
· Oracle Data Integrator 11g
You should have the above Requirements should be installed on your System before going to attempt this task.
And you should already finish simple interface exercise
Description:
This Tutorial will help you to ‘Load source Data table EMP with condition Commission is NOT NULL into target table TRG_EMP_FIL3 through ODI’ .
Explanation:
Connect to SQL Developer, Here My source schema is SRC_SCOTT in which we have the source table ‘EMP’ and My target schema is TRG_SCOTT in which we have the target table ‘TRG_EMP_FIL3’(contains no data) which is shown in the below screenshots:
Connect to Oracle Data Integrator by running the Shortcut Menu or Go to the location where the ODI resides and run it.
Now Login to Oracle Data Integrator by providing the login details
Now in the Designer Navigator expand your project and right click on the Interface and select ‘New Interface’
Give the name for the Interface and click on Mapping tab.
We can see the Source table ‘EMP’ in the source Model ‘SRC_SCOTT_MODEL’ and target table ‘TRG_EMP_FIL3’ in the target Model ‘TRG_SCOTT_MODEL’ as shown in the below screenshots.
Now drag and drop source table ‘EMP’ into source area and target table ‘TRG_EMP_FIL3’ into target area , after dragging the target table TRG_EMP_FIL3 it will ask you to do the automate mapping(which is shown in the below screenshot)., cilck ‘YES’ then the target table will automatically mapped with the source table as shown below:
Check whether all the columns of the target table TRG_EMP_FIL3 is mapped with the source table EMP columns.
Now in the source table EMP which is in Source area drag the COMM column ,Filter Properties will open.
In the active Filter Provide the condition ‘EMP.COMM IS NOT NULL’
Click on ‘Right symbol’ to check whether the provided condition is correct or not.
Here the Above provided condition is correct which is shown in the below:
Now click on the ‘Flow’ tab and then click on the ‘Flow Diagram of the target table’ then you will be able to see the target Properties.
In the Target Properties.,For IKM Selector select the ‘IKM SQL control append Knowledge Module’( we have to Import this IKM (Integrated Knowledge Module) before going to create the interface.
For the Options ‘Flow Control’ select ‘False’ (It must me true only when we are using Some constraints or conditions.,In this case it should be False otherwise it throws an Error) as shown below:
For the Option ‘Truncate’ select ‘True’ (Because it truncates the target table if it contains any data,If it contains no data then it inserts the Source table data) as shown below:
Goto File menu and click on ‘Save’ to save the Interface.,While saving ODIwill ask to lock the object to prevent other users from editing it.,Click on ‘No’.
Click on ‘Execute’ button(which will be in the Green Symbol on top of the Navigator tools) to run the Interface.
It will show the “Execution” dialogue box which is shown below.,here select the Context as ‘Global’ and agent as ‘Local Agent’ (because we have not yet created any standalone agent).Click on ‘Ok’ to start Execution.
It will show an Information Message as ‘Session Started’.Click on ‘OK’.
Now go to the Operator navigator and expand the Date and expand the Today,click on ‘Refresh icon’(Which is in Blue Symbol on below the Navigator tools), then you will be able to see the Status of the Interface Execution.We can see the Session details like Number of Inserts,Number of Rows,Number of Updates .. from this session details which is shown Below:
From the above Session details we can observe that Our target table contains(4 Records,because here No. of inserts is 4).
Now go to SQL developer and check whether the target table TRG_EMP_FIL3 contains 4 records.
Yes,From the above screenshot we can see that our target table TRG_EMP_FIL3 contains 4 records(which is the Source table EMP data whose COMM is NOT NULL )
Using this tutorial,We have successfully got to know how to load from Source table EMP with condition COMM is NOT NULL which is in SRC_SCOTT schema into Target table TRG_EMP_FIL3 which is in TRG_SCOTT schema using Oracle Data Integrator.
Thanks & Regards
WWW.ODIGURUS.COM
