SUPPORT / SAMPLES & SAS NOTES
Problem Note 62805: %TKMI_JOB_RO etl_end_service task generates "Caused by: java.sql.SQLSyntaxErrorException: ORA-01795: maximum number of expressions in a list is 1000"
 |  |  |  |  |
SAS® Promotion Optimization might generate the following error in the MerchIntelGridSvr log file during the %TKMI_JOB_RO etl_end_service task:
<YYYY-MM-DD HH:MM:SS,sss> [http-bio-<port>-exec-<n>-<n>] ERROR com.sas.solutions.di.tkmiserver.data.DataServiceImpl - Exceptions occurred during Cleanup RDBMS After BATCH OPT/PREP
<YYYY-MM-DD HH:MM:SS,sss> [http-bio-<port>-exec-<n>-<n>] ERROR com.sas.solutions.di.tkmiserver.data.DataServiceImpl - could not execute native bulk manipulation query
<YYYY-MM-DD HH:MM:SS,sss> [http-bio-<port>-exec-<n>-<n>] DEBUG com.sas.solutions.di.tkmiserver.data.DataServiceImpl - could not execute native bulk manipulation query
org.hibernate.exception.SQLGrammarException: could not execute native bulk manipulation query
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:92)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
at org.hibernate.engine.query.NativeSQLQueryPlan.performExecuteUpdate(NativeSQLQueryPlan.java:219)
at org.hibernate.impl.SessionImpl.executeNativeUpdate(SessionImpl.java:1300)
at org.hibernate.impl.SQLQueryImpl.executeUpdate(SQLQueryImpl.java:365)
at com.sas.solutions.di.tkmiserver.data.DataServiceImpl$GenericUpdateSQLWorker.run(DataServiceImpl.java:1550)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1152)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:622)
at java.lang.Thread.run(Thread.java:748)
Caused by: java.sql.SQLSyntaxErrorException: ORA-01795: maximum number of expressions in a list is 1000
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1059)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:522)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:257)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:587)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:225)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:53)
at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:943)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1150)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:4798)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:4875)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeUpdate(OraclePreparedStatementWrapper.java:1361)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:102)
at org.hibernate.engine.query.NativeSQLQueryPlan.performExecuteUpdate(NativeSQLQueryPlan.java:210)
... 6 more
<YYYY-MM-DD HH:MM:SS,sss> [http-bio-<port>-exec-<n>-<n>] ERROR com.sas.solutions.di.tkmiserver.data.DataServiceImpl - could not execute native bulk manipulation query
<YYYY-MM-DD HH:MM:SS,sss> [http-bio-<port>-exec-<n>-<n>] DEBUG com.sas.solutions.di.tkmiserver.data.DataServiceImpl - could not execute native bulk manipulation query
org.hibernate.exception.SQLGrammarException: could not execute native bulk manipulation query
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:92)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
at org.hibernate.engine.query.NativeSQLQueryPlan.performExecuteUpdate(NativeSQLQueryPlan.java:219)
at org.hibernate.impl.SessionImpl.executeNativeUpdate(SessionImpl.java:1300)
at org.hibernate.impl.SQLQueryImpl.executeUpdate(SQLQueryImpl.java:365)
at com.sas.solutions.di.tkmiserver.data.DataServiceImpl$GenericUpdateSQLWorker.run(DataServiceImpl.java:1550)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1152)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:622)
at java.lang.Thread.run(Thread.java:748)
Caused by: java.sql.SQLSyntaxErrorException: ORA-01795: maximum number of expressions in a list is 1000
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1059)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:522)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:257)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:587)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:225)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:53)
at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:943)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1150)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:4798)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:4875)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeUpdate(OraclePreparedStatementWrapper.java:1361)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:102)
at org.hibernate.engine.query.NativeSQLQueryPlan.performExecuteUpdate(NativeSQLQueryPlan.java:210)
... 6 more
This error is likely to occur when a promotion plan has more than 1,000 geo-prods in it.
Click the Hot Fix tab in this note to access the hot fix for this issue.
Operating System and Release Information
| Product Family | Product | System | Product Release | SAS Release | ||
| Reported | Fixed* | Reported | Fixed* | |||
| SAS System | SAS Promotion Optimization | Microsoft® Windows® for x64 | 5.4 | 9.4 TS1M4 | ||
| Linux for x64 | 5.4 | 9.4 TS1M4 | ||||