Troubleshooting The Use Of Sequences With ODI
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.
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.
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).
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