ODI 12.2.1 Mapping Upgraded from ODI 10g/11g Fails with "java.sql.SQLException: ORA-30484: missing window specification for this function" and is Missing Aggregate Functions In Generated Code (Doc ID 2159550.1)

Last updated on MAY 12, 2017

Applies to:

Oracle Data Integrator - Version 12.2.1.0.0 and later
Information in this document applies to any platform.

Symptoms

An Oracle Data Integrator (ODI) 10g / 11g Interface uses aggregate functions in the "case when" block.

After the Repository upgrade from ODI 11g to ODI 12.2.1, the same upgraded ODI 12.2.1 Mapping (Interface) execution generates code like below:

...
where
(1=1)
AND (TST_GDM_G18_0001.IP_ID=TST_GDM_INPA_CTRN.ORIGIN_IP_ID)
AND (SUBSTR(TST_GDM_G18_0001.ARR_ID, 9, 7) = TDWCNCL.ACCT_NBR (+)
AND TST_GDM_G18_0001.BRANCH_ID = TDWCNCL.BRANCH_ID (+)
AND TST_GDM_G18_0001.IP_ID = TDWCNCL.IP_ID (+))
AND (TDWCNCL.COLL_DOVKOD=TST_GDM_G18_0005.CURR_CODE (+)
AND TDWCNCL.EXP_DATE = TST_GDM_G18_0005.CURR_DATE (+))
and (TDWCNCL.COLLATERAL_TYPE_ID = 58)
and (TDWCNCL.COLL_STATUS <> 'C')
and (TDWCNCL.UPDATE_DATE < TO_DATE('#GAR_ENTERPRISEDATAWAREHOUSE.VAR_TWS_DATE_OYMD','DD.MM.YYYY') + 1)

The original ODI 11g Integration Interface execution which works correctly generates the code like below for the same part:

...
where
(1=1)
AND (TST_GDM_G18_0001.IP_ID=TST_GDM_INPA_CTRN.ORIGIN_IP_ID)
And (SUBSTR(TST_GDM_G18_0001.ARR_ID, 9, 7) = TDWCNCL.ACCT_NBR (+)
AND TST_GDM_G18_0001.BRANCH_ID = TDWCNCL.BRANCH_ID (+)
AND TST_GDM_G18_0001.IP_ID = TDWCNCL.IP_ID (+))
AND (TDWCNCL.COLL_DOVKOD=TST_GDM_G18_0005.CURR_CODE (+)
AND TDWCNCL.EXP_DATE = TST_GDM_G18_0005.CURR_DATE (+))
And (TDWCNCL.COLLATERAL_TYPE_ID (+) = 58)
And (TDWCNCL.COLL_STATUS (+) <> 'C')
And (TDWCNCL.UPDATE_DATE (+) < TO_DATE('#GAR_ENTERPRISEDATAWAREHOUSE.VAR_TWS_DATE_OYMD','DD.MM.YYYY') + 1)
Group By TST_GDM_INPA_CTRN.FINAL_IP_ID

From the comparison between the ODI 11g generated cod,e and ODI 12.2.1 generated code, the ODI 12.2.1 generated code is missing the following:

(+) ...

Group By ...

The upgraded ODI 12.2.1 mapping execution also fails with the following exception:

ODI-1228: Task Insert new rows-IKM SQL Control Append- fails on the target connection XXXXX. Caused By: java.sql.SQLException: ORA-30484: missing window specification for this function
 at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:466)
 at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:407)
 at ...
 at oracle.odi.runtime.agent.execution.sql.SQLCommand.execute(SQLCommand.java:205)
 at oracle.odi.runtime.agent.execution.sql.SQLExecutor.execute(SQLExecutor.java:141)
 at oracle.odi.runtime.agent.execution.sql.SQLExecutor.execute(SQLExecutor.java:28)
 at oracle.odi.runtime.agent.execution.TaskExecutionHandler.handleTask(TaskExecutionHandler.java:52)
 at oracle.odi.runtime.agent.execution.SessionTask.processTask(SessionTask.java:206)
 at oracle.odi.runtime.agent.execution.SessionTask.doExecuteTask(SessionTask.java:117)
 at oracle.odi.runtime.agent.execution.AbstractSessionTask.execute(AbstractSessionTask.java:886)
 at oracle.odi.runtime.agent.execution.SessionExecutor$SerialTrain.runTasks(SessionExecutor.java:2227)
 at oracle.odi.runtime.agent.execution.SessionExecutor.executeSession(SessionExecutor.java:607)
 at oracle.odi.runtime.agent.processor.TaskExecutorAgentRequestProcessor$1.doAction(TaskExecutorAgentRequestProcessor.java:718)
 at oracle.odi.runtime.agent.processor.TaskExecutorAgentRequestProcessor$1.doAction(TaskExecutorAgentRequestProcessor.java:611)
 at oracle.odi.core.persistence.dwgobject.DwgObjectTemplate.execute(DwgObjectTemplate.java:203)
 at oracle.odi.runtime.agent.processor.TaskExecutorAgentRequestProcessor.doProcessStartAgentTask(TaskExecutorAgentRequestProcessor.java:800)
 at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.access$1400(StartSessRequestProcessor.java:74)
 at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$StartSessTask.doExecute(StartSessRequestProcessor.java:702)
 at oracle.odi.runtime.agent.processor.task.AgentTask.execute(AgentTask.java:180)
 at oracle.odi.runtime.agent.support.DefaultAgentTaskExecutor$2.run(DefaultAgentTaskExecutor.java:108)
 at java.lang.Thread.run(Thread.java:745)

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms