In Oracle 19c, I’m facing a foreign key constraint violation (ORA-02291) during a MERGE operation on table pp, but a standalone DELETE / UPDATE works fine on the same record.

Error message:

ORA-02291: integrity constraint (cust.fk_pp_01) violated - parent key not found

Context:

• pp.req (FK) references rr.req (parent table).
• FK on pp and PK on rr are both ENABLED but NOT VALIDATED.

All_Constraints table details:

 - DEFERRABLE = NOT DEFERRABLE
 - DEFERRED = IMMEDIATE

MERGE source is a left join of a collection (TABLE(?)) and table p.

Issue occurs only when p has no data for the given mek.

Issue is not replicable in lower environments.

Merge Statement:

 > MERGE INTO pp dest  
 >     USING (  
 >         SELECT column_value mek, DECODE(p.mek, NULL, 'Y', 'N') delete_ind  
 >         FROM TABLE(sys.odcinumberlist(100000678)) tab, p WHERE p.mek(+) = tab.column_value  
 >     ) src  
 >     ON (dest.mek = src.mek)  
 >     WHEN MATCHED THEN  
 >     UPDATE SET edit_y = 'Y', maker = 10102311  
 >     DELETE WHERE src.delete_ind = 'Y';

Observations:

• The case that failed on production, only one mek is being processed in the collection variable and it has a valid req which exist in the rr table since 2020.
• 99% of records work fine, but a few fail with ORA-02291.
• Standalone DELETE / UPDATE on the same record works without issues.
• No orphan records, and req is nullable
• Reviewed the table data and structure with peers, DBA team, no clues

Question:

1. Are there any known Oracle bugs with MERGE and Foreign key constraint?
2. Any workarounds to prevent false FK violations?