My Oracle Support Banner

Troubleshooting The Use Of Sequences With ODI (Doc ID 423872.1)

Last updated on AUGUST 02, 2019

Applies to:

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

Symptoms

What are the possible approaches to use Sequences in Oracle Data Integrator (ODI) and how to troubleshoot their usage?

After defining an ODI Sequence to map the primary key column in an ODI 12c Mapping (or ODI 11g / 10g Integration Interface), certain messages are signaled. What are they due to?

Case 1. Unique constraint violated

java.sql.SQLException: ORA-00001: unique constraint ... violated

This message is signaled when a Source Datastore is implemented in Oracle and the mapping for the column containing the sequence is set on the Staging Area (which is also in Oracle).

Case 2. Sequence executed on Source

java.sql.SQLException: [SQL0418] Use of parameter marker is not valid.
at com.ibm.as400.access.JDError.throwSQLException(JDError.java)

This message is signaled when the Source Datastore is implemented in DB2/400 and the mapping for the column containing the sequence is set on the Source.

Case 3. Sequence executed on default (staging area) in ODI 12c (Execute on Hint : No hint)

ORA-2287 : sequence number not allowed here

This message is signaled at runtime when the Sequence is set on the default (staging area) and referenced in a sub-select statement of (for example) the Insert into flow table step of an ODI 12c Mapping (or ODI 11g / 10g Integration Interface).

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
 Using ODI Sequences in ODI 12c and ODI 11g, starting with ODI 11.1.1.5.0
 Using ODI Sequences in ODI 11.1.1.3.0, ODI 10g, and legacy Sunopsis
 Using Native Database Sequences in ODI 12c, ODI 11g, ODI 10g and legacy Sunopsis
 (a) Using Oracle Sequences
 (b) Using DB2/400 Sequences
 (c) Using Microsoft SQL Server, Sybase, Teradata... Identity columns
 Using Sequences with Incremental Update IKM
References

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