My Oracle Support Banner

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 FEBRUARY 05, 2019

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 (<SCHEMA1>.<TABLE>=<SCHEMA2>.<COL1>)
AND (SUBSTR(<SCHEMA1>.ID, 9, 7) = <SCHEMA2>.NBR (+)
AND <SCHEMA1>.B_ID = <SCHEMA2>.B_ID (+)
AND <SCHEMA1>.I_ID = <SCHEMA1>.I_ID (+))
AND (<SCHEMA3>.COLL=<SCHEMA2>.CURR_CODE (+)
AND <SCHEMA3>.EXP_DATE = <SCHEMA2>.CURR_DATE (+))
and (<SCHEMA3>.COLL_TYPE_ID = <ID>)
and (<SCHEMA3>.COLL_STATUS <> 'C')
and (<SCHEMA3>.UPDATE_DATE < TO_DATE('#<PROJECT_NAME>.<VAR_NAME>','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 (<SCHEMA1>.I_ID=<SCEHMA2>.I_ID)
And (SUBSTR(<SCHEMA1>.ID, 9, 7) = <SCEHMA2>.NBR (+)
AND <SCHEHMA1>.B_ID = <SCHEMA2>.B_ID (+)
AND <SCHEMA3>.I_ID = <SCEHMA2>.I_ID (+))
AND (<SCHEMA2>L.COLL=<SCEHMA1>.CURR_CODE (+)
AND <SCHEMA3>.EXP_DATE = <SCHEMA1>.CURR_DATE (+))
And (<SCHEMA3>.COLL_TYPE_ID (+) = <ID>)
And (<SCHEMA3>.COLL_STATUS (+) <> 'C')
And (<SCHEMA3>.UPDATE_DATE (+) < TO_DATE('#<PROJECT_NAME>.<VAR_NAME>','DD.MM.YYYY') + 1)
Group By <SCHEMA3>.FINAL_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

To view full details, sign in with your My Oracle Support account.

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


In this Document
Symptoms
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.