How to fix Unique Constraint Violation in ODI
Abotts Logo Abotts Partners with singapore based tech giant to help migrate their public sector customer from Sybase to SQL server.
Upworks Logo Abotts partners with NYPL to integrate with their partner libraries.
Abotts Logo ABOTTS partners with County in Los Angeles to upgrade their court infrastructure into new technologies.
Upworks Logo Upworks Inc partners with ABOTTS to build their Oracle Cloud Infrastructure (OCI) and migrate their custom applications to OCI.
Abotts partners with startup to manage and maintain their IT infrastructure and support SOC2 reporting.
Gnorth Logo Abotts Inc Partners with Gnorth consulting to deploy exadata and ODA for a large public sector customer.
Abotts Logo Abotts Partners with singapore based tech giant to help migrate their public sector customer from Sybase to SQL server.
Upworks Logo Abotts partners with NYPL to integrate with their partner libraries.
Abotts Logo ABOTTS partners with County in Los Angeles to upgrade their court infrastructure into new technologies.
Upworks Logo Upworks Inc partners with ABOTTS to build their Oracle Cloud Infrastructure (OCI) and migrate their custom applications to OCI.
Abotts partners with startup to manage and maintain their IT infrastructure and support SOC2 reporting.
Gnorth Logo Abotts Inc Partners with Gnorth consulting to deploy exadata and ODA for a large public sector customer.

How to fix Unique Constraint Violation in ODI

The ORA-00001: unique constraint (DEVODIC_ODI_REPO.AK_REV_FOR_TABLE) violated

Error:

com.sunopsis.dwg.SQLWorkReposException: ORA-00001: unique constraint (DEVODIC_ODI_REPO.AK_REV_FOR_TABLE) violated

         at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:510)

         at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:462)

         at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1105)

         at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:551)

         at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:269)

         at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:656)

         at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:271)

         at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:92)

         at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:971)

         at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1280)

         at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3719)

         at oracle.jdbc.driver.T4CPreparedStatement.executeInternal(T4CPreparedStatement.java:1427)

         at oracle.jdbc.driver.OraclePreparedStatement.executeLargeUpdate(OraclePreparedStatement.java:3809)

         at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3789)

         at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeUpdate(OraclePreparedStatementWrapper.java:1064)

         at oracle.ucp.jdbc.proxy.oracle$1ucp$1jdbc$1proxy$1oracle$1StatementProxy$2oracle$1jdbc$1internal$1OraclePreparedStatement$$$Proxy.executeUpdate(Unknown Source)

         at sun.reflect.GeneratedMethodAccessor141.invoke(Unknown Source)

         at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

         at java.lang.reflect.Method.invoke(Method.java:498)

         at oracle.odi.core.datasource.support.RuntimeClassLoaderDataSourceCreator$StatementInvocationHandler.invoke(RuntimeClassLoaderDataSourceCreator.java:173)

         at com.sun.proxy.$Proxy58.executeUpdate(Unknown Source)

         at com.sunopsis.sql.SnpsQuery.executeUpdate(SnpsQuery.java:689)

         at com.sunopsis.dwg.dbobj.generated.GeneratedSnpRevForTable.insertAction(GeneratedSnpRevForTable.java:345)

         at com.sunopsis.dwg.DwgObject.insert(DwgObject.java:3722)

         at com.sunopsis.dwg.DwgObject.insert(DwgObject.java:3756)

         at com.sunopsis.dwg.reverse.Reverse.populateRevForTable(Reverse.java:3459)

         at com.sunopsis.graphical.frame.edit.EditFrameSnpModel$12.doLongOperation(EditFrameSnpModel.java:3203)

         at oracle.cef.ui.Framework$5.doInBackground(Framework.java:726)

         at oracle.cef.ui.Framework$5.doInBackground(Framework.java:718)

         at javax.swing.SwingWorker$1.call(SwingWorker.java:295)

         at java.util.concurrent.FutureTask.run(FutureTask.java:266)

         at javax.swing.SwingWorker.run(SwingWorker.java:334)

         at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)

         at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)

         at java.lang.Thread.run(Thread.java:750)

Caused by: Error : 1, Position : 0, Sql = insert into SNP_REV_FOR_TABLE (IND_REV,I_MOD,MOD_GUID,TABLE_NAME,TABLE_TYPE) values (:1 ,:2 ,:3 ,:4 ,:5 ), OriginalSql = insert into SNP_REV_FOR_TABLE (IND_REV,I_MOD,MOD_GUID,TABLE_NAME,TABLE_TYPE) values (?,?,?,?,?), Error Msg = ORA-00001: unique constraint (DEVODIC_ODI_REPO.AK_REV_FOR_TABLE) violated

 

         at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:514)

         … 34 more

 

Cause of the Error

1. Duplicate Data Entry:

-> The system is trying to insert a record into SNP_REV_FOR_TABLE where the combination of TABLE_NAME and MOD_GUID already exists

-> AK_REV_FOR_TABLE is a unique constraint that prevents duplicate combinations of these columns

 

2. ODI-Specific Causes:

-> Multiple reverse engineering attempts on the same table without clearing previous results

-> Corrupted metadata in the ODI repository

-> Concurrent reverse engineering operations trying to insert the same metadata

-> Failed or interrupted previous reverse engineering that left incomplete data

 

Solution

1. Through ODI Studio (Recommended Method):

  1. Open ODI Studio
  2. Navigate to the Models panel
  3. Right-click on the affected model
  4. Select “Clear Reverse-Engineering Results”
  5. Try your reverse engineering operation again

 

2. If that doesn’t work, you can try clearing the cache:

  1. Close ODI Studio
  2. Clear the ODI cache folder (typically in user home directory)
  3. Restart ODI Studio
  4. Attempt the operation again

 

3. If still experiencing issues, you can check and fix through SQL:

— First check for duplicate entries

SELECT TABLE_NAME, MOD_GUID, COUNT(*)

FROM SNP_REV_FOR_TABLE

GROUP BY TABLE_NAME, MOD_GUID

HAVING COUNT(*) > 1;

 

— If duplicates exist, you can remove them

DELETE FROM SNP_REV_FOR_TABLE

WHERE ROWID NOT IN (

    SELECT MIN(ROWID)

    FROM SNP_REV_FOR_TABLE

    GROUP BY TABLE_NAME, MOD_GUID

);

 

4. If none of the above work:

  1. Export your model metadata
  2. Delete the model completely
  3. Recreate the model
  4. Import the metadata back

 

5. Last resort (if you have proper backups):

— Completely clean the table and start fresh

TRUNCATE TABLE SNP_REV_FOR_TABLE;

 

Important Safety Notes:

  • Always backup your ODI repository before making any direct database changes
  • Make sure no other users are actively using the system when performing these operations
  • Document any changes you make in case you need to rollback
  • If possible, test these solutions in a development environment first

 

Conclusion

The ORA-00001 error occurs due to a violation of a unique constraint, typically caused by duplicate entries being inserted into the SNP_REV_FOR_TABLE table. By investigating and cleaning up duplicates, adjusting the reverse engineering process, and using deduplication logic in your queries, you can resolve the issue and prevent it from occurring again. 

Proactive maintenance of the ODI repository and proper configuration of the reverse engineering process are critical to ensuring smooth operations.

 

External reference