Review result fails to save the changes made in RSA Via Lifecycle and Governance with error ORA-00001: unique constraint (AVUSER.PK_T_REVIEW_COMP_CH_ID) violated error

3 years ago

Originally Published: 2016-04-20

Article Number

000064621

Applies To

RSA Product Set: Via Lifecycle and Governance (Via L&G)
RSA Product/Service Type: All

Issue

After maintaining or revoking the review item, when you try to save or sign off the changes, it will fail to save the changes with the error shown below:

The text of the error is:

Error editing the Review

Failed to save the review components.

You will observe the error below in the aveksaServer.log Accessing the aveksaServer.log file for RSA Lifecycle and Governance (L&G):

ERROR (SaveReviewChangesThread11757-14313266) [com.aveksa.gui.pages.review.review.newEdit.SaveReviewChangesThread]
Failed to signoff review components
------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------
Caused by: com.aveksa.server.db.PersistenceException: insert into T_AV_REVIEW_COMP_CHANGES
("REVIEW_COMPONENT_ID", "CREATION_DATE", "REVIEWER", "STATE", "COMMENTS", "EXPIRATION_DATE",
"OPERATION_TYPE", "COMMITTED_HISTORY_ID", "ROLE_TYPE", "ID") values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
at com.aveksa.server.db.persistence.PersistenceServiceProvider.processInGivenTXUsingUpdate
(PersistenceServiceProvider.java:1905)
at com.aveksa.server.db.PersistenceManager.processInGivenTXUsingUpdate
(PersistenceManager.java:312)
at com.aveksa.server.review.Review.savePassedReviewComponent(Review.java:2221)
... 3 more
------------------------------------------------------------------------------------------------------------------
Caused by: java.sql.BatchUpdateException: ORA-00001: unique constraint 
(AVUSER.PK_T_REVIEW_COMP_CH_ID) violated

Cause

Execute the following queries on the database with the avuser[database user] login using SQL Developer or through a remote connection and using the “sqlplus username/password” command.

SELECT max(id) FROM t_av_review_comp_changes;
SELECT REVIEW_COMP_CHANGE_SEQUENCE.nextVal FROM dual;

You will find the value returned from the second query is smaller than the value from first one. The value from second query should be ideally greater than or equal to the value from the first. The max IDs need to be in sync with the sequence.

Resolution

After executing the queries above, execute query in step 2 using the avuser[database user] login using

SQL Developer; or
A remote connection and using “sqlplus username/password” command.

If the result of the second query is less than the value of the results of the first query, increment the sequence as required. To do this, execute the query below:

ALTER SEQUENCE REVIEW_COMP_CHANGE_SEQUENCE INCREMENT BY <value>;

Increment the REVIEW_COMP_CHANGE_SEQUENCE by a value so that it becomes more than value shown from the first query. For example,

ALTER SEQUENCE REVIEW_COMP_CHANGE_SEQUENCE INCREMENT BY 100;

Restart the server after running the ALTER command since sequence values are cached.

Don't see what you're looking for?

Related Articles

Trending Articles