When using SAS^® Risk and Finance Workbench with Oracle 12cR2 as the back-end database, you might be unable to save data to a worksheet or perform other common tasks. These issues are known to occur when you use Oracle 12c and Oracle 12cR2.

 

This issue can occur when you try to save data to a worksheet in the user interface. In this scenario, an exception similar to the following can occur:

org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [insert into data_point_value (data_point_key, period_id, data_point_val, data_point_value_cd, source_cd, source_key, scope_cd, iteration_sk, created_user_id,
created_dttm, revision_num, property_def_sk, source_txt) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, null, ?, ?)];
nested exception is java.sql.BatchUpdateException: ORA-00932: inconsistent datatypes: expected NUMBER got CLOB
   at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:97)
   at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
   at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
   at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
   at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:605)
   at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:617)
   at org.springframework.jdbc.core.JdbcTemplate.batchUpdate(JdbcTemplate.java:890)

ERROR com.sas.stress.rest.controller.ControllerExceptionHandler - Generic
exception handling for request:
https://< server >/SASRiskandFinanceWorkbench/rest/config/hierarchies
java.lang.ArrayIndexOutOfBoundsException: 8
   at oracle.jdbc.driver.OracleSql.computeBasicInfo(OracleSql.java:950)
   at oracle.jdbc.driver.OracleSql.getSqlKind(OracleSql.java:623)
   at oracle.jdbc.driver.OraclePreparedStatement.<init>(OraclePreparedStatement.java:1212)

Caused by: java.sql.SQLSyntaxErrorException: ORA-00936: missing expression
   at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:447)
   at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
   at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:951)
   at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:513)
   at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:227)
   at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)
   at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:208)
   at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:886)
   at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:117
   at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:129
   at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3613)
   at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3657)
   at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1495)
   at org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:646)
   at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:589)

The workaround is the same for all of these scenarios: