Review result throwing Exception while executing filter query due to ORA-00904 in RSA Governance & Lifecycle

[#1]

Article Number

000068215

Applies To

This is a known issue in the following versions.

• RSA Governance & Lifecycle 7.x

Issue

While Checking the review results, a "Request Could not be handled" message was returned, and aveksaServer.log shows an "Exception while executing filter query" caused by below:
ORA-00904: "CACHE_"."APP_XXXXX": invalid identifier

06/08/2023 07:51:01.983 ERROR (default task-1107) [com.aveksa.server.filter.BaseReviewComponentFilter] Exception running the following review component count query: 

06/08/2023 07:51:01.984 ERROR (default task-1107) [com.aveksa.gui.objects.review.ReviewComponentObjectStore] 
com.aveksa.server.filter.FilterException: Error while fetching record counts
	at com.aveksa.server.filter.BaseReviewComponentFilter.executeCountCriteria(BaseReviewComponentFilter.java:337)
	at com.aveksa.server.filter.ObjectListFilter.getCountOfFilteredObjects(ObjectListFilter.java:130)

Caused by: com.aveksa.server.db.PersistenceException: Executing JDBC query failed [com.aveksa.server.db.persistence.PersistenceServiceProvider.executeJDBCQuery(PersistenceServiceProvider.java:2961)
	at com.aveksa.server.db.persistence.PersistenceServiceProvider.executeJDBCQueryInteger(PersistenceServiceProvider.java:3096)
	at com.aveksa.server.db.PersistenceManager.executeJDBCQueryInteger(PersistenceManager.java:413)
	at com.aveksa.server.filter.BaseReviewComponentFilter.executeCountCriteria(BaseReviewComponentFilter.java:333)
	... 85 more
Caused by: org.hibernate.exception.SQLGrammarException: error executing work
	at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:63)
	at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42)
	at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:109)
	at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:95)
	at org.hibernate.engine.jdbc.internal.JdbcCoordinatorImpl.coordinateWork(JdbcCoordinatorImpl.java:337)
	at org.hibernate.internal.SessionImpl.doWork(SessionImpl.java:2153)
	at org.hibernate.internal.SessionImpl.doReturningWork(SessionImpl.java:2149)
	at com.aveksa.server.db.persistence.PersistenceServiceProvider.executeJDBCQuery(PersistenceServiceProvider.java:2942)
	... 88 more
Caused by: java.sql.SQLSyntaxErrorException: ORA-00904: "CACHE_"."ACCT_LAST_REVIEWED_DATE": invalid identifier

	at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:509)
	at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:461)
	at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1104)
	at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:550)
	at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:268)
	at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:655)
	at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:270)
	at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:91)
	at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:807)
	at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:983)
	at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1168)
	at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3666)
	at oracle.jdbc.driver.T4CPreparedStatement.executeInternal(T4CPreparedStatement.java:1426)
	at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3713)
	at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1167)
	at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:504)
	at com.aveksa.server.db.persistence.PersistenceServiceProvider$4.execute(PersistenceServiceProvider.java:2949)
	at com.aveksa.server.db.persistence.PersistenceServiceProvider$4.execute(PersistenceServiceProvider.java:2942)
	at org.hibernate.jdbc.WorkExecutor.executeReturningWork(WorkExecutor.java:55)
	at org.hibernate.internal.SessionImpl$4.accept(SessionImpl.java:2146)
	at org.hibernate.engine.jdbc.internal.JdbcCoordinatorImpl.coordinateWork(JdbcCoordinatorImpl.java:332)
	...  more
Caused by: Error : 904, Position : 7426, Sql =  Error Msg = ORA-00904: "CACHE_"."ACCT_LAST_REVIEWED_DATE": invalid identifier

	at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:513)
	... 111 more

Image description
 

Cause

There is a customized attribute with a similar display name to RSA standard OOTB attributes and that caused a conflict between this customized attribute and standard OOTB attributes.

Resolution

- Execute the below query to identify any existing customized attribute with a name similar to RSA standard OOTB attributes, You can compare the returned display name with the one that existed in the error message as well.

select * from avuser.t_av_custom_attributes where display_name in
(select display_name from avuser.t_av_custom_attributes where is_OOTB_ATTR = 'y') and is_OOTB_ATTR = 'n';

- Navigate to Admin > Attributes and re-name the customized attribute with a name similar to RSA standard OOTB attributes.