In SAS Forecast Studio 4.1 (M1), the following error occurs if you run sample reports when the Web Infrastructure Platform is configured on an Oracle database:

[1] org.springframework.jdbc.BadSqlGrammarException: SqlMapClient operation; bad SQL grammar []; nested exception is com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred while applying a parameter map.
--- Check the JES.insertTask-InlineParameterMap.
--- Check the statement (update failed).
--- Cause: java.sql.SQLSyntaxErrorException: ORA-00904: "PROMPT_VALUES_TXT": invalid identifier
at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslateSQLErrorCodeSQLExceptionTranslator.java:233)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate

This error occurs because there is a typographical error in the SAS_JES_TASKS table schema for Oracle. The identifier PROMPT_VALUES_TX should be PROMPT_VALUES_TXT. To resolve this error, follow these steps:

1. Back up the schema-jes.sql file, which resides in the directory \SASWebInfrastructurePlatform\9.3\Config\Deployment\Data\oracle.
2. Edit schema-jes.sql, as follows:
   • Search for the text PROMPT_VALUES_TX and replace it with PROMPT_VALUES_TXT.
   • Search for the text INPUT_VALUE_TXT and remove the string NOT NULL enable, which follows INPUT_VALUE_TXT.
3. Stop the SAS^® middle-tier services (for example, Remote Services, the JBoss application server, and so on)
4. Use the SAS^® to remove the configuration for the Web Infrastructure Platform.
5. Contact your Oracle database administrator and request that all tables be removed from the Oracle schema that was specified as the database in the Web Infrastructure Platform configuration.
6. Reconfigure the Web Infrastructure Platform using the SAS Deployment Wizard.
7. Restart the SAS middle-tier services and your web application server.

After you apply these steps (which are necessary), a different error occurs when you try to run the sample reports.

org.springframework.dao.DataIntegrityViolationException: SqlMapClient operation;
SQL [];
--- The error occurred in com/sas/svcs/jobexecution/dao/persistence/JES.xml.
--- The error occurred while applying a parameter map.
--- Check the JES.insertTaskInputValue-InlineParameterMap.
--- Check the statement (update failed).
--- Cause: java.sql.SQLIntegrityConstraintViolationException: ORA-01400: cannot
insert NULL into ("SAS_SSVCS"."SAS_JES_TASK_INPUT_VALUES"."INPUT_VALUE_TXT")
; nested exception is com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred in com/sas/svcs/jobexecution/dao/persistence/JES.xml.
--- The error occurred while applying a parameter map.
--- Check the JES.insertTaskInputValue-InlineParameterMap.
--- Check the statement (update failed).
--- Cause: java.sql.SQLIntegrityConstraintViolationException: ORA-01400: cannot
insert NULL into ("SAS_SSVCS"."SAS_JES_TASK_INPUT_VALUES"."INPUT_VALUE_TXT")

A hot fix is available to correct this second error. Click the Hot Fix tab in this note to access the hot fix for this issue.

Operating System and Release Information

A fix for this issue for SAS Middle Tier 9.3_M1 is available at: