Getting Inconsistent Results and ORA-02291

(Doc ID 2152187.1)

Last updated on JULY 05, 2016

Applies to:

Oracle Retail Price Management - Version 14.0.2 and later
Information in this document applies to any platform.


In Oracle Retail Price Management (RPM) 14.0.2, we note that is producing inconsistent results for event_type=SP with status=W vs. status=E.  Furthermore, an ORA-02291 ("integrity constraint violated") error occurs.

Steps to Recreate:

  1. Create a promotion through the price injector in worksheet status.
  2. Promo is moved to history table by RPM promo archive batch.
  3. Execute injector batch for Simple Promotion (SP) with status W.  Batch terminates successfully.  Notice the records in stage table have 'Error' status and include error message (expected).
    The records are set to status E and the Error message column is populated by the injector batch after it completes (expected).
    So in step 4, we are actually processing the same records that were processed in step 3, only the status is different.
  4. Execute injector batch for SP with status E. Batch aborts with integrity constraint error:
ORA-02291: integrity constraint (RMS14.RPO_RPR_FK) violated - parent key not found returned by program unit

Furthermore, the records in the stage table have been set to Worksheet status and have no error message. Since the records in steps 3 and 4 are the same, and only the status is different, it is expected that the result of step 4 should be the same as step 3 (which is setting the records to E and error message column populated).

The injector batch should be able to retry the records regardless of W or E status and yield the same result as step 3. However, for the same records in stage table, when status is W, the above ORA error is not encountered.


Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms