Aggregator Component Missing from Mappings after Upgrading ODI 11g to 12c (Doc ID 2619538.1)

Last updated on JUNE 22, 2023

Applies to:

Symptoms

After upgrading an interface from Oracle Data Integrator (ODI) 11g to an ODI 12c mapping, execution of the mapping fails with the following error:

ODI-1227: Task Insert new rows-IKM SQL to File Append- fails on the source connection <ORACLE_STAGING>.
Caused By: java.sql.SQLSyntaxErrorException: ORA-00937: not a single-group group function
  at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:495)
  at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:447)
  at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1055)
  at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:624)
  at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:253)
  at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:613)
  at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:214)
  at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:38)
  at oracle.jdbc.driver.T4CStatement.executeForDescribe(T4CStatement.java:728)
  at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:965)
  at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1157)
  at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:1370)
  at oracle.jdbc.driver.OracleStatementWrapper.executeQuery(OracleStatementWrapper.java:367)
  at oracle.odi.query.JDBCTemplate.executeQuery(JDBCTemplate.java:196)
  at oracle.odi.query.JDBCTemplate.executeQuery(JDBCTemplate.java:204)
  at oracle.odi.runtime.agent.execution.sql.SQLDataProvider.readData(SQLDataProvider.java:100)
  at oracle.odi.runtime.agent.execution.sql.SQLDataProvider.readData(SQLDataProvider.java:38)
  at oracle.odi.runtime.agent.execution.Executor.handleDataMovementTask(Executor.java:309)
  at oracle.odi.runtime.agent.execution.DataMovementTaskExecutionHandler.handleTask(DataMovementTaskExecutionHandler.java:51)
  at oracle.odi.runtime.agent.execution.SessionTask.processTask(SessionTask.java:216)
  ...
Caused by: Error : 937, Position : 11, Sql =
  select
  FILE1_A.C1 C1,
  (sum(FILE1_A.C2)) C2,
  (sum(FILE1_A.C3)) C3
  from <ORACLE_STAGING_SCHEMA>.C$_<UNIQUE_ID> FILE1_A
  where
    (1=1)
    and (lower(FILE1_A.C1) not like  '%test%')
    , OriginalSql =
  select
     FILE1_A.C1 C1,
     (sum(FILE1_A.C2)) C2,
     (sum(FILE1_A.C3)) C3
  from <ORACLE_STAGING_SCHEMA>.C$_<UNIQUE_ID> FILE1_A
  where
    (1=1)
    and (lower(FILE1_A.C1) not like  '%test%')
    , Error Msg = ORA-00937: not a single-group group function
  at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:499)
  ... 32 more

The interface was successful in ODI 11g, and consists of a source file that loads a target file, through several filters and aggregations on columns, like sum().

Cause