My Oracle Support Banner

Import of Procedure Using Synonym INSERT_UPDATE Mode Causes a Corrupted ODI Work Repository (Doc ID 2270221.1)

Last updated on SEPTEMBER 30, 2020

Applies to:

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


When attempting to import a Procedure with synonym INSERT_UPDATE or synonym INSERT into a different Oracle Data Integrator (ODI) Work Repository where the parent Folder and / or Project doesn't exist, the Procedure seems to be imported, but is not associated to a Folder and Project, thereby causing a corrupted Work Repository.

The issue is confirmed for both ODI and

To reproduce:

  1. Have two Work Repositories, WORK1 and WORK2.
  2. Create a simple Procedure in Work Repository WORK1.
  3. Export the Procedure from WORK1.
  4. Import the Procedure into another Project Folder in Work Repository WORK2, using synonym INSERT_UPDATE or INSERT mode.
  5. Observe the procedure is imported, but it does not display in the Project Folder to which it was imported.

    Looking at the SNP_TRT table, the Procedure was imported, but it has a "null" value for both parent ID, and Folder ID.


This corruption will then prevent the later import of this Work Repository, see <Document 2238227.1> - "ODI-14070 Parent Object Was Not Found" When Importing Work Repository into ODI 12.2.1.

In other cases where Oracle Business Intelligence Enterprise Edition (OBIEE) is used, using through Refresh Lineage wizard may fail with a "unique constraint" error:

java.sql.BatchUpdateException: ORA-00001: unique constraint
at oracle.jdbc.driver.OraclePreparedStatement.executeBatch(
at oracle.jdbc.driver.OracleStatementWrapper.executeBatch(
at sun.reflect.GeneratedMethodAccessor100.invoke(Unknown Source)


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.