My Oracle Support Banner

ODI Main Package Failed - Dimension Key error - unique constraint (DW_REPORTING.DIM_CATEGORY_PK) violated (Doc ID 2353290.1)

Last updated on MARCH 29, 2019

Applies to:

Oracle Knowledge - Version 8.6 and later
Information in this document applies to any platform.


If ODI fails in a spot that causes a sequence number to get out of synch you will see an error like this.

ODI-1226: Step INT-LOAD_DIM_CATEGORY fails after 1 attempt(s).
ODI-1240: Flow INT-LOAD_DIM_CATEGORY fails while performing a Integration operation. This flow loads target table DIM_CATEGORY.
ODI-1228: Task INT-LOAD_DIM_CATEGORY (Integration) fails on the target ORACLE connection ORACLE_SERVER.
Caused By: java.sql.SQLIntegrityConstraintViolationException: ORA-00001: unique constraint (DW_REPORTING.DIM_CATEGORY_PK) violated

at oracle.jdbc.driver.T4CTTIoer.processError(
at oracle.jdbc.driver.T4CTTIoer.processError(
at oracle.jdbc.driver.T4C8Oall.processError(


This happens because the sequence has not been kept in synch with the table.  The sequence has to always be one ahead of the last row.  There are many sequences in the reporting database and any one of them could get out of synch.


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

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