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

Last updated on JUNE 28, 2016

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

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