My Oracle Support Banner

Inconsistent Generation NOTES_SEQUENCE Value on NOTES Table - May Result in "ORA-00001: unique constraint (ASAP.IDX_DOC_SEQ) violated" Error. (Doc ID 2846505.1)

Last updated on FEBRUARY 07, 2022

Applies to:

Oracle Communications MetaSolv Solution - Version 6.3.0 to 6.3.1 [Release 6.3]
Information in this document applies to any platform.

Symptoms

MetaSolv Solution version 6.3.0

The same process is not always used for generating the NOTES_SEQUENCE value, when notes are created for orders in the application . In some instances, the NOTES_SEQUENCE is generated by finding the MAX(NOTES_SEQUENCE) value for the DOCUMENT_NUMBER and adding 1. In other cases, the NOTES_SEQUENCE is set to the same value as the NOTES_ID value (which is pulled from the SQ_NOTES_ID sequence). This has the potential to cause issues with the IDX_DOC_SEQ unique index when the current sequence values matches a value that was previously created as MAX(NOTES_SEQUENCE+1).

The application is running in a 2 node RAC database environment. Each instance caches a set of values from the sequence. The issue intermittently observed is that notes may get generated for an order in instance #1, then additional notes may be generated in instance #2, so the IDs pull from each respective cache. However, if a NOTES_SEQUENCE is created using MAX+1, that value may overlap with an unused sequence value from the other cache. If the sequence value from the other cache is then applied to the current order, it causes a ORA-00001 unique constraint violation on IDX_DOC_SEQ because the NOTES_SEQUENCE value already exists for the order.

 

ERROR

A data-related error occurred. Please use the detail button for more information.

sqlerror:1
java.sql.SQLIntegrityConstraintViolation: ORA-00001: unique constraint
(ASAP.IDX_DOC_SEQ) violated ORA-06512: at
"ASAP.SP_WRITE_AUDIT_NOTE_BY_USER", line 45 ORA-06512: at
"ASAP.TR_AUDIT_TASK_INSERT", line 35 ORA-04088: error during execution of trigger
"ASAP.TR_AUDIT_TASK_INSERT"

 

Cause

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
Symptoms
Cause
Solution
References


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