CALLING FUNCTION IN ODI

STEP 1 : Creating and Reverse Engineering Source

1. We can use EMP table of SDBU schema as source
2. Reverse engineer EMP table into SOURCE  MODEL

STEP 2 : Creating and Reverse Engineering Target

1. Create target table in Target Schema using below script

Create Table Tgt_function  (     Empno  Number ,     Ename  Varchar2(20),     Sal    Number ,     Deptno Number ,     Dname  Varchar2(20)  )

2. Reverse engineer into TARGET MODEL

STEP 3 : Create database function

1. Create Database Function

CREATE OR REPLACE  FUNCTION RR_GET_DNAME     (      pDEPTNO IN NUMBER)     RETURN VARCHAR2  IS     DNAME VARCHAR2(11);  BEGIN     SELECT DNAME INTO DNAME FROM DEPT WHERE DEPTNO = pDEPTNO;     RETURN(DNAME);  END;

STEP 4 : Creating Interface/Mapping

1. Create interface with the name of INT_CALL_FUNCTION
2. Click on mapping tab → Drag and drop source (EMP)and target (Tgt_function)in respective locations

3. Select DNAME column → In properties window → under implementation → Click on Edit → call the function as shown → click on ok
4. 
5. Flow tab
6. Select Target and select IKM SQL Control Append
7. Select FLOW_CONTROL = False

8. Click on SAVE
9. Click on RUN
10. Observe output
11. 
12. 

ODI 12c Installation

1. Install Oracle 11.2.0.3/4

2. http://rritec.blogspot.in/2014/09/02-oracle-database-installation.html
3. JDK 7
4. Download ODI12 C
1. http://www.oracle.com/technetwork/middleware/data-integrator/downloads/index.html
5. Extract as shown below

5. In Welcome Screen click on NEXT

6. Provide required  installation directory or leave default → Click on NEXT

7. Select Enterprise installation → Click on NEXT

8. Click on NEXT

9. Click on NEXT

10. Click on NEXT

11. Click on FINISH

DATA SETS

DataSets

Using UNION ,INTERSECT ,UNION ALL MINUS

STEP 1 : Creating and Reverse Engineering Source

1. Connect to Source schema (SCOTT)
2. Execute below scripts

create table EMP10 AS select * from emp where deptno=10 create table EMP20 AS select * from emp where deptno=20

3. We can use EMP table of SCOTT schema as source
4. Reverse engineer EMP10 and EMP20  table into SOURCE  MODEL

STEP 2 : Creating and Reverse Engineering Target

1. Connect to Target Schema (TDBU)

create table EMP1020 AS select * from scott.emp where 1=2

2. Reverse engineer into TARGET MODEL

STEP 3 : Creating Interface/Mapping

1. Create interface with the name of m_Data_Sets
2. Click on mapping tab → Drag and drop source (EMP10)and target (EMP1020) in respective locations

3. Click on add/Remove Data Set → Click on Add new dataset
4. change name as EMP20 → select  operator as UNION → Click on Close

5. Drag and drop EMP20 source into source work area
6. 
7. Click on Flow tab
8. Select Target and select IKM SQL CONTROL APPEND
9. Select FLOW_CONTROL = False

10. Click on SAVE
11. Click on RUN
12. Observe output
13. 

Sorting in ODI 11G

Sorting in ODI 11G

1. It is equal to ORDER BY clause in SQL
2. it is available as a component in ODI 12 C

STEP 1 : Creating and Reverse Engineering Source

1. We can use EMP table of SCOTT schema as Source

STEP 2 : Creating and Reverse Engineering Target

1. Duplicate EMP data store and rename as TGT_EMP_SORT
2. Drag and drop into Target Model

STEP 3 : Change KM

1. Duplicate the existing KM IKM SQL Control Append
2. Rename as IKM SQL CONTROL APPEND SORT
3. Open this KM → Select Details → Double Click on Insert New Rows → end of the code add below code

<%=odiRef.getOption("ORDER_BY")%>

4. Right Click on IKM SQL CONTROL APPEND → Select New Option

5. Provide name and type  as shown → save and close the KM

STEP 4 : Creating Interface/Mapping

1. Create interface with the name of m_SORT_IN_ODI11G
2. Click on mapping tab → Drag and drop source EMP  and target  TGT_EMP_SORT in respective locations

3. Click on Flow tab
4. Select Target and select IKM SQL Control Append Sort → Change options
1. ORDER_BY : ORDER BY DEPTNO ,SAL
2. FLOW_CONTROL : False
3. Truncate :True
4. Create Target Table :True

5. Click on SAVE
6. Click on RUN → Observe Code

7. Observe Target Table data