PK HALF_DATA Violated When Processing A Huge File (Doc ID 3065291.1)

Last updated on JANUARY 02, 2025

Applies to:

Symptoms

As part of E2E2 mock conversions, customer is trying to load a file for the Half_Data entity and they got an error when execute the POM job CONV_HALF_DATA_PROCESS_ADHOC:
2024-11-05T22:59:11,731 [Thread-40] INFO AbstractShellJobExecutor - EXT_PROG_SYS_OUT:@RMSAlias: @0PACKAGE_ERROR@1ORA-00001: unique constraint (RMS01.PK_HALF_DATA) violated@2CON .

In the first run 9924 records processed successfully and 76 errors (expected errors). Then, the process crashed with the above error.
The errored were changed and processed records 'Data_val_status' in the CONV_HALF_DATA table to 'X' to process the next 10k chunk, but it fails again with the same unique constraint error.
It seems that the package is hardcoding the 10k chunk records and each time that it went to the next 10k chunk, it's losing track of the PK_HALF_DATA fields that were processed before (which originates the unique constraint issue).
 
The HALF_DATA_BUDGET data entity could have the same approach.

Customer checked and confirm that have no any duplicate in the file and they only have 1 file for the HALF_DATA entity, with ~11 millions rows.

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Upload the zip file to FTS (incoming folder) -> 10008 records.
2. Run Job CONV_HALF_DATA_LOAD_JOB_ADHOC, to load the data in the table CONV_HALF_DATA -> all 10008 records are loaded in CONV_HALF_DATA.
3. Run Job CONV_HALF_DATA_JOB_ADHOC, to process the data in the table HALF_DATA.
4. See only 10000 records processed:
- 8701 records successfully loaded in HALF_DATA - status 'P'
- 1299 records with error and not loaded in HALF_DATA - status 'E'.
Even if customer has no any duplicate, in POM logs they can see the error "ORA-00001: unique constraint (RMS01.PK_HALF_DATA) violated@2CONV_HALF_DATA_SQL.EXEC_HALF_DATA_UPD".

Cause

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