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.