I know how to use months_between in SQL - my issue is with ODI 12c, I have the expression MONTHS_BETWEEN(CURDATE(), INCR_TEST.DOB) in ODI but when I run the mapping , it takes out the CURDATE() and changes the months_between to MONTHS_BETWEEN(INCR_TEST.DOB, INCR_TEST.DOB).
You are correct. ODI is not able to parse it properly. I did a quick test and got the same result like you. The 1st column is getting replaced by second column. Please talk with Oracle Support team. I will let you know if i get any fix for this.
I couldn't replicate that behaviour with 22.214.171.124 and target DB Oracle 126.96.36.199
I only know that CURDATE() and SYSDATE() don't work. SYSDATE does work though.
As a quick workaround for your problem, I would try one of these two operations :
- Create a user function with another syntax. In the implementation you can put the oracle implementation.
- Or trick the parser by printing MONTHS_BETWEEN so it won't see it on the first pass :
Hope it helps.
Jerome, its on ODI 12c(188.8.131.52.0).
ODI 11g handles it properly. You will not find any issue there.
curdate() will work in sql server or mysql. Its not oracle given function. Anyway this not the concern now. Have a check with ODI 12C.
For example in ODI 12c:
Just hardcode below expression in mapping section of any interface.
MONTHS_BETWEEN (TO_DATE ('2004/03/01', 'yyyy/mm/dd'), TO_DATE ('2003/02/01', 'yyyy/mm/dd') )
Then see the generated code in operator. You find like below.
/*+ APPEND PARALLEL */
INTO SCOTT.EMP1 (
EMPNO , JOB , MGR , HIREDATE , COMM , DEPTNO , SAL
/*+ MERGE */
EMP.EMPNO , EMP.JOB , EMP.MGR , sysdate , EMP.COMM , EMP.DEPTNO , MONTHS_BETWEEN( TO_DATE('2003/02/01', 'yyyy/mm/dd') , TO_DATE('2003/02/01', 'yyyy/mm/dd') )
You're right, I read it too fast .
The two workarounds suggested might still work though.
I tested it today and <??> scriptlet did the trick.
Sorry to open up this again, I was trying to write a user function in Oracle and calling it to calculate the age ( instead of using months_between() directly) in the mapping.
CREATE OR REPLACE FUNCTION xx_monthsbetween(d date)
WHEN OTHERS THEN
In the target mapping column, I am invoking the function by putting "DW_TARGET.xx_monthsbetween(DATE_TAB.DOB)". The simulated query looks fine and runs without any error in SQL developer but fails in the mapping with the below error.
ODI-1217: Session New_Mapping_DS_SESS (2539) fails with return code ODI-1298.
ODI-1226: Step New_Mapping_DS_STEP fails after 1 attempt(s).
ODI-1298: Serial task "null (10)" failed because child task "null (30)" is in error.
ODI-1298: Serial task "DW_TRG_UNIT (20)" failed because child task "null (30)" is in error.
ODI-1228: Task IKM Oracle Insert (Load W_DATE_TAB) fails on the target ORACLE connection LRS.
Caused By: java.sql.SQLSyntaxErrorException: ORA-00904: "XX_MONTHSBETWEEN": invalid identifier
Could it be a privilege problem? Try granting execute to the schema used to connect in the dataserver definition (physical topology).
GRANT EXECUTE ON xx_monthsbetween TO <schema_name>;
Don't forget to mark the question as solved
Thank you. Its working now :-)
You can also use an ODI user function for that kind of things .
Can you please tell me how to do using ODI user function.