'ORA-01722: invalid number' or 'Non supported SQL92 token at position: XXX' errors in the aveksaServer.log when rendering or editing/creating forms with dynamic variables in RSA Identity Governance & Lifecycle

[#1]

Article Number

000038634

Applies To

RSA Product Set: RSA Identity Governance & Lifecycle
RSA Version/Condition: 7.0.x, 7.1.x, 7.2.0
 

Issue

One of the below errors are observed in the aveksaServer.log file ($AVEKSA_HOME/wildfly/standalone/log/aveksaServer.log) after creating a change request that uses a custom request form. These same errors are observed when editing and/or creating a form field.
 

at com.aveksa.server.db.persistence.PersistenceServiceProvider.executeJDBCQuery(PersistenceServiceProvider.java:2992)
        at com.aveksa.server.db.persistence.PersistenceServiceProvider.executeJDBCQueryInteger(PersistenceServiceProvider.java:3127)
        at com.aveksa.server.db.PersistenceManager.executeJDBCQueryInteger(PersistenceManager.java:439)
        at com.aveksa.gui.components.table.special.SQLTable.getObjectCount(SQLTable.java:330)
        ... 79 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:2973)
        ... 82 more
Caused by: java.sql.SQLSyntaxErrorException: ORA-01722: invalid number

  or

at com.aveksa.server.db.persistence.PersistenceServiceProvider.executeJDBCQuery(PersistenceServiceProvider.java:2992)
        at com.aveksa.server.db.persistence.PersistenceServiceProvider.executeJDBCQueryInteger(PersistenceServiceProvider.java:3127)
        at com.aveksa.server.db.PersistenceManager.executeJDBCQueryInteger(PersistenceManager.java:439)
        at com.aveksa.gui.components.table.special.SQLTable.getObjectCount(SQLTable.java:330)
        ... 79 more
Caused by: org.hibernate.exception.GenericJDBCException: error executing work
        at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:47)
        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:2973)
        ... 82 more
Caused by: java.sql.SQLException: Non supported SQL92 token at position: 2300

Please refer to RSA Knowledge Base Article 00003027 -- Artifacts to gather in RSA Identity Governance & Lifecycle to find the location of the aveksaServer.log file for your specific deployment if you are on a WildFly cluster or a non-WildFly platform. The aveksaServer.log may also be downloaded from the RSA Identity Governance & Lifecycle user interface (Admin > System > Server Nodes tab > under Logs.)
 

Cause

This is a known issue reported in engineering ticket ACM-102221.

This issue occurs when a request form field has a table Control Type with a filter that cannot be resolved until the form is triggered or the form has valid context. (Requests > Configuration > Request Forms > {form name} > Fields > Edit/New).

In this example the Control Type is a User Account Table and the Account Filter has the following advanced Where clause:

x."Account Name" in (select a.name from t_av_user_account_mappings map inner join t_av_accounts a on a.id = map.account_id 
where map.user_id = '${avform.requestor.Id}' and 
map.deletion_date is null and a.deletion_date is null)

or

x."Account Name" in (select a.name from t_av_user_account_mappings map inner join t_av_accounts a on a.id = map.account_id 
where map.user_id = ${avform.requestor.Id} and 
map.deletion_date is null and a.deletion_date is null)

Note the dynamic form field ${avform.requestor.Id}. When this field is surrounded by single quotes ('${avform.requestor.Id}'), the error is:

ORA-01722: invalid number

When the single quotes are removed (${avform.requestor.Id}) the error is:

Non supported SQL92 token at position: 2300

 

Resolution

This issue is resolved in the following RSA Identity Governance & Lifecycle patches:

• RSA Identity Governance & Lifecycle 7.1.1 P07
• RSA Identity Governance & Lifecycle 7.2.0 P02