I couldn't replicate that behaviour with 126.96.36.199 and target DB Oracle 188.8.131.52
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(184.108.40.206.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') )
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