本地Oracle数据库启动Camunda时出现ORA-00933错误及排查进展

更新（问题根源定位）：已确认问题出在类路径中存在H2数据库依赖，导致Camunda启动时自动适配了H2的SQL语法，而非Oracle的语法，最终执行了带有LIMIT ? OFFSET ?的SQL语句——这在Oracle中是不支持的语法，从而抛出ORA-00933错误。

问题场景 #

• 本地环境使用空Oracle数据库启动Camunda时，Flyway脚本执行正常，但后续日志中出现SQL语法错误
• 相同构建包部署到远程空Oracle数据库时，无此报错

错误详情 #

数据库查询错误，原因：java.sql.SQLSyntaxErrorException: ORA-00933: SQL命令未正确结束

完整错误堆栈：

### 错误可能存在于org/camunda/bpm/engine/impl/mapping/entity/Job.xml ### 错误可能涉及org.camunda.bpm.engine.impl.persistence.entity.JobEntity.selectNextJobsToExecute-Inline ### 错误发生在设置参数时 ### SQL: select RES.* from ACT_RU_JOB RES where (RES.RETRIES_ > 0) and (RES.DUEDATE_ is null or RES.DUEDATE_ <= ?) and (RES.LOCK_OWNER_ is null or RES.LOCK_EXP_TIME_ < ?) and RES.SUSPENSION_STATE_ = 1 and ( ( RES.EXCLUSIVE_ = 1 and not exists( select J2.* from ACT_RU_JOB J2 where J2.PROCESS_INSTANCE_ID_ = RES.PROCESS_INSTANCE_ID_ -- from the same proc. inst. and (J2.EXCLUSIVE_ = 1) -- also exclusive and (J2.LOCK_OWNER_ is not null and J2.LOCK_EXP_TIME_ >= ?) -- in progress ) ) or RES.EXCLUSIVE_ = 0 ) LIMIT ? OFFSET ? ### Cause: java.sql.SQLSyntaxErrorException: ORA-00933: SQL command not properly ended at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30) at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:150) at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:141) at org.camunda.bpm.engine.impl.db.sql.DbSqlSession.selectList(DbSqlSession.java:95) at org.camunda.bpm.engine.impl.db.entitymanager.DbEntityManager.selectListWithRawParameter(DbEntityManager.java:173) at org.camunda.bpm.engine.impl.db.entitymanager.DbEntityManager.selectList(DbEntityManager.java:165) at org.camunda.bpm.engine.impl.db.entitymanager.DbEntityManager.selectList(DbEntityManager.java:161) at org.camunda.bpm.engine.impl.db.entitymanager.DbEntityManager.selectList(DbEntityManager.java:150) at org.camunda.bpm.engine.impl.persistence.entity.JobManager.findNextJobsToExecute(JobManager.java:191) at org.camunda.bpm.engine.impl.cmd.AcquireJobsCmd.execute(AcquireJobsCmd.java:54) at org.camunda.bpm.engine.impl.cmd.AcquireJobsCmd.execute(AcquireJobsCmd.java:34) at org.camunda.bpm.engine.impl.interceptor.CommandExecutorImpl.execute(CommandExecutorImpl.java:24) at org.camunda.bpm.engine.impl.interceptor.CommandContextInterceptor.execute(CommandContextInterceptor.java:104) ... 8 common frames omitted Caused by: java.sql.SQLSyntaxErrorException: ORA-00933: SQL command not properly ended 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.executeForDescribe(T4CPreparedStatement.java:774) at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:925) at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1111) at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:4798) at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:4901) at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1385) at sun.reflect.GeneratedMethodAccessor104.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.apache.tomcat.jdbc.pool.StatementFacade$StatementProxy.invoke(StatementFacade.java:114) at com.sun.proxy.$Proxy144.execute(Unknown Source) at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:63) at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:79) at org.apache.ibatis.executor.BatchExecutor.doQuery(BatchExecutor.java:93) at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:324) at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156) at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:109) at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:83) at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:148) ... 19 common frames omitted

解决建议 #

移除类路径中的H2数据库依赖，确保Camunda能够正确识别Oracle数据库并使用对应的SQL语法（比如Oracle的分页语法FETCH FIRST N ROWS ONLY或者基于ROWNUM的分页逻辑）。

内容的提问来源于stack exchange，提问作者user1321466