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
2 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:
User-added image

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
  1. 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.
  1. 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;
  1. Restart the server after running the ALTER command since sequence values are cached.

Related Articles

Trending Articles

Don't see what you're looking for?