Wednesday, December 14, 2016

How to create user defined function in ODI 12c?

Scenario:
For a give float number I need to find out decimal length and precision length

TEST (SOURCE)
N_FORMAT VARCHAR2(10)

TEST_TRG (TARGET)
N_FORMAT VARCHAR2(10)
DEC_LEN NUMBER(7)
FRACT_LEN NUMBER(3)

Assumptions:
1.Aware of creating model
2.Aware of creating Interface (table to table load)

Steps:
1.Go to Designer Navigator --> Go to Project Accordion --> Expand Project --> Right click on User Functions and select New User Function
2. Definition Tab
Name : DEC_LEN
Group : LENGTH
Syntax:
      DEC_LEN($(P1))

[Note:  Syntax : <function_name>(<parameter_list>)

3. Implementation Tab
Click on Plus Symbol. Provide following code
(CASE WHEN $(P1) IS NULL THEN 0
     WHEN INSTR($(P1),'.') = 0 THEN LENGTH($(P1))
     ELSE INSTR($(P1),'.')-1
END)

Select Technology as "oracle"


4. Similarly create one more function as mention below
Definition Tab
Name : FRACT_LEN
Group : LENGTH
Syntax:
      FRACT_LEN($(P1))



5.Implementation Tab
(CASE WHEN $(P1) IS NULL THEN 0
       WHEN INSTR($(P1),'.') = 0 THEN 0
     ELSE LENGTH($(P1))-INSTR($(P1),'.')
END)

Select Technology as "oracle"



6.In the interface mapping should be as below:

Output:


1 comment:

  1. TARGET N_FORMA IS NOT NUMBER(10,3) IT IS VARCHAR2(10) PLEASE IGNORE DATATYPE IN THE MAPPING SCREEN SHOT

    ReplyDelete