ORA-210000 Oracle error: ORA-00001: unique constraint (NEOL_PROD.V01_NMSBROADLOGRCP_ID) violated

[Divya104]

The workflow fails at the email delivery for a recurring one, with the above mentioned erroe, has anyone faced similar issue before ? IT says Broadlogrcp_ID violated, but these are running from long time, whats the possibilities that we have unique constraint error now , after multiple runs.

[LakshmiPravallika]

Hi @Divya104 ,

Please check whether the Broad Log ID in the Recurring Delivery Logs matches with the BroadlogID(@id) in the nms:broadLogRcp (Recipient delivery log) Schema.

If yes, the record will not be inserted due to duplicate ID getting inserted into  nms:broadLogRcp Schema. You can try rerunning the Workflow or change the existing ID in nms:broadLogRcp (Recipient delivery log) Schema to a different ID and run the WF again.

Regards,

Pravallika.

[akshaaga · Accepted solution]

@Divya104 ,

Essentially the user causes the error when trying to execute an INSERT or UPDATE statement that has generated a duplicate value in a restricted field. The error can commonly be found when a program attempts to insert a duplicate row in a table.

You can try to add a deduplication activity before the update data activity.

Set it to deduplicate on the table key (NMSBROADLOGRCP_ID) and any other unique constraints present.

[ParthaSarathy]

Hi @Divya104 ,

Whenever a delivery is executed with 'nms:recipient' as target mapping, for each recipient in the delivery, a broadLog record will get created in nms:broadLogRcp schema, and each record will be having an unique primary key (It uses NmsBroadLogId sequence for Primary key generation.)

In a very rare scenario, There might be a possibility that if multiple delivery runs at same time, it may assign same primary key to two different broadLogRcp records of different delivery. So 1 record will get inserted successfully, and another record's delivery will get failed with the error 'unique constraint violated'. But again, this kind of error will happen in a rare scenario.

Solution:

To solve this error, just restarting the workflow will fix the issue. Or as a permanent solution, you can either increase or decrease your Recurring workflow's scheduler to 3 minutes, so that it wont overlap with other deliveries.