My Oracle Support Banner

ODI 12c Error "ORA-02287: sequence number not allowed here" Due to Database 'Nextval' Included in Aggregate Function of Mapping (Doc ID 2437323.1)

Last updated on MAY 27, 2020

Applies to:

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

Symptoms

NOTE: In the example below, user details / company name / address / email / telephone number represent a fictitious sample. Any similarity to actual persons, living or dead, is purely coincidental and not intended in any manner.

In Oracle Data Integrator (ODI) 12.2.1.3, when a native database sequence is in the target expression for a column (i.e. [sequence].nextval), and an aggregate component exists in the mapping, the generated SQL is wrong and the following error is generated:

ODI-1228: Task Insert new rows-IKM Oracle Insert-Load DEFAULT_TARGET fails on the target connection ORACLE_SR.
Caused By: java.sql.SQLSyntaxErrorException: ORA-02287: sequence number not allowed here
  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.executeForRows(T4CStatement.java:891)
  at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1194)
  at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:1835)
  at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1790)
  at oracle.jdbc.driver.OracleStatementWrapper.execute(OracleStatementWrapper.java:301)
  at oracle.odi.runtime.agent.execution.sql.SQLCommand.execute(SQLCommand.java:205)
  at oracle.odi.runtime.agent.execution.sql.SQLExecutor.execute(SQLExecutor.java:142)
  at oracle.odi.runtime.agent.execution.sql.SQLExecutor.execute(SQLExecutor.java:28)
  at oracle.odi.runtime.agent.execution.TaskExecutionHandler.handleTask(TaskExecutionHandler.java:52)
  ...
Caused by: Error : 2287, Position : 139, Sql =

INSERT
/*+ APPEND PARALLEL */
INTO SCOTT.ORACLE_SR_TARGET
(
PK_ID ,
SALARY_SUM ,
DEPT
)
SELECT
SEQ_ORACLE_SR.NEXTVAL ,
(SUM(ORACLE_SR_EMP.SALARY)) ,
ORACLE_SR_EMP.DEPT
FROM
SCOTT.ORACLE_SR_EMP ORACLE_SR_EMP
GROUP BY
ORACLE_SR_EMP.DEPT , OriginalSql =
INSERT
/*+ APPEND PARALLEL */
INTO SCOTT.ORACLE_SR_TARGET
(
PK_ID ,
SALARY_SUM ,
DEPT
)
SELECT
SEQ_ORACLE_SR.NEXTVAL ,
(SUM(ORACLE_SR_EMP.SALARY)) ,
ORACLE_SR_EMP.DEPT
FROM
SCOTT.ORACLE_SR_EMP ORACLE_SR_EMP
GROUP BY
ORACLE_SR_EMP.DEPT , Error Msg = ORA-02287: sequence number not allowed here

  at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:499)
  ... 30 more

Changes

 

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
Changes
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.