Support for Oracle Database 23ai LTS

[toco-cam]

Acceptance Criteria (Required on creation)

• Oracle Database 23ai LTS is supported

Hints

• Support for Oracle Database 23ai LTS Cloud was released in September
• On-premise released https://blogs.oracle.com/database/post/oracle-23ai-now-generally-available

Links

• https://github.com/camunda/team-infrastructure/issues/538
• https://forum.camunda.io/t/does-camunda-support-for-oracle-23c-upgrade/51165
• https://forum.camunda.io/t/camunda-7-support-for-oracle-23c/50986

Breakdown

### Pull Requests
- [ ] https://github.com/camunda/cambpm-jenkins-shared-library/pull/146
- [ ] https://github.com/camunda/camunda-docs-manual/pull/1674
- [ ] https://github.com/camunda/camunda-bpm-platform/pull/4522
- [ ] https://github.com/camunda/camunda-bpm-platform-ee/pull/972

Dev2QA handover

• Does this ticket need a QA test and the testing goals are not clear from the description? Add a Dev2QA handover comment

[kaminduN]

Hi,
Any update on the Oracle Database 23c support. Is this scheduled for a future release ?
We referred Camunda documentation and couldn't get an idea if its compatible or not.

Thank you

[toco-cam]

Hey @kaminduN,

we are waiting for the final release: Linux x86-64 | 1H CY2024 before we can commit hopefully for 7.22.

Does this help?

Regards Tobias

[tasso94]

Problem 1

Description

All tests failed with a similar exception:

Failure

[2024-08-13T21:08:47.971Z] [ERROR] shouldFailQueryByTenantIdNull(org.camunda.bpm.engine.test.api.multitenancy.query.history.MultiTenancyHistoricIncidentQueryTest)  Time elapsed: 0.356 s  <<< ERROR!
[2024-08-13T21:08:47.972Z] org.camunda.bpm.engine.ProcessEnginePersistenceException: An exception occurred in the persistence layer. Please check the server logs for a detailed message and the entire exception stack trace.
[2024-08-13T21:08:47.972Z] 	at org.camunda.bpm.engine.impl.util.ExceptionUtil.wrapPersistenceException(ExceptionUtil.java:341)
[2024-08-13T21:08:47.972Z] 	at org.camunda.bpm.engine.impl.util.ExceptionUtil.doWithExceptionWrapper(ExceptionUtil.java:336)
[2024-08-13T21:08:47.972Z] 	at org.camunda.bpm.engine.impl.db.sql.DbSqlSession.selectOne(DbSqlSession.java:128)
[2024-08-13T21:08:47.972Z] 	at org.camunda.bpm.engine.impl.db.entitymanager.DbEntityManager.selectOne(DbEntityManager.java:185)
[2024-08-13T21:08:47.972Z] 	at org.camunda.bpm.engine.impl.cmd.PurgeDatabaseAndCacheCmd.purgeDatabase(PurgeDatabaseAndCacheCmd.java:92)
[2024-08-13T21:08:47.972Z] 	at org.camunda.bpm.engine.impl.cmd.PurgeDatabaseAndCacheCmd.execute(PurgeDatabaseAndCacheCmd.java:63)
[2024-08-13T21:08:47.972Z] 	at org.camunda.bpm.engine.impl.cmd.PurgeDatabaseAndCacheCmd.execute(PurgeDatabaseAndCacheCmd.java:45)
[2024-08-13T21:08:47.972Z] 	at org.camunda.bpm.engine.impl.interceptor.CommandExecutorImpl.execute(CommandExecutorImpl.java:28)
[2024-08-13T21:08:47.972Z] 	at org.camunda.bpm.engine.impl.interceptor.CommandContextInterceptor.execute(CommandContextInterceptor.java:111)
[2024-08-13T21:08:47.972Z] 	at org.camunda.bpm.engine.impl.interceptor.ProcessApplicationContextInterceptor.execute(ProcessApplicationContextInterceptor.java:70)
[2024-08-13T21:08:47.972Z] 	at org.camunda.bpm.engine.impl.interceptor.CommandCounterInterceptor.execute(CommandCounterInterceptor.java:34)
[2024-08-13T21:08:47.972Z] 	at org.camunda.bpm.engine.impl.interceptor.LogInterceptor.execute(LogInterceptor.java:33)
[2024-08-13T21:08:47.972Z] 	at org.camunda.bpm.engine.impl.interceptor.ExceptionCodeInterceptor.execute(ExceptionCodeInterceptor.java:55)
[2024-08-13T21:08:47.972Z] 	at org.camunda.bpm.engine.impl.ManagementServiceImpl.purge(ManagementServiceImpl.java:286)
[2024-08-13T21:08:47.973Z] 	at org.camunda.bpm.engine.impl.test.TestHelper.assertAndEnsureCleanDbAndCache(TestHelper.java:365)
[2024-08-13T21:08:47.973Z] 	at org.camunda.bpm.engine.impl.test.TestHelper.assertAndEnsureCleanDbAndCache(TestHelper.java:346)
[2024-08-13T21:08:47.973Z] 	at org.camunda.bpm.engine.test.ProcessEngineRule.finished(ProcessEngineRule.java:251)
[2024-08-13T21:08:47.973Z] 	at org.junit.rules.TestWatcher.finishedQuietly(TestWatcher.java:122)
[2024-08-13T21:08:47.973Z] 	at org.junit.rules.TestWatcher.access$400(TestWatcher.java:52)
[2024-08-13T21:08:47.973Z] 	at org.junit.rules.TestWatcher$1.evaluate(TestWatcher.java:70)
[2024-08-13T21:08:47.973Z] 	at org.camunda.bpm.engine.test.ProcessEngineRule$1.evaluate(ProcessEngineRule.java:191)
[2024-08-13T21:08:47.973Z] 	at org.junit.rules.RunRules.evaluate(RunRules.java:20)
[2024-08-13T21:08:47.973Z] 	at org.junit.runners.ParentRunner$3.evaluate(ParentRunner.java:306)
[2024-08-13T21:08:47.973Z] 	at org.junit.runners.BlockJUnit4ClassRunner$1.evaluate(BlockJUnit4ClassRunner.java:100)
[2024-08-13T21:08:47.973Z] 	at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:366)
[2024-08-13T21:08:47.973Z] 	at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:103)
[2024-08-13T21:08:47.973Z] 	at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:63)
[2024-08-13T21:08:47.973Z] 	at org.junit.runners.ParentRunner$4.run(ParentRunner.java:331)
[2024-08-13T21:08:47.973Z] 	at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:79)
[2024-08-13T21:08:47.973Z] 	at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:329)
[2024-08-13T21:08:47.973Z] 	at org.junit.runners.ParentRunner.access$100(ParentRunner.java:66)
[2024-08-13T21:08:47.973Z] 	at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:293)
[2024-08-13T21:08:47.973Z] 	at org.junit.runners.ParentRunner$3.evaluate(ParentRunner.java:306)
[2024-08-13T21:08:47.973Z] 	at org.junit.runners.ParentRunner.run(ParentRunner.java:413)
[2024-08-13T21:08:47.973Z] 	at org.apache.maven.surefire.junit4.JUnit4Provider.execute(JUnit4Provider.java:365)
[2024-08-13T21:08:47.973Z] 	at org.apache.maven.surefire.junit4.JUnit4Provider.executeWithRerun(JUnit4Provider.java:273)
[2024-08-13T21:08:47.974Z] 	at org.apache.maven.surefire.junit4.JUnit4Provider.executeTestSet(JUnit4Provider.java:238)
[2024-08-13T21:08:47.974Z] 	at org.apache.maven.surefire.junit4.JUnit4Provider.invoke(JUnit4Provider.java:159)
[2024-08-13T21:08:47.974Z] 	at org.apache.maven.surefire.booter.ForkedBooter.invokeProviderInSameClassLoader(ForkedBooter.java:384)
[2024-08-13T21:08:47.974Z] 	at org.apache.maven.surefire.booter.ForkedBooter.runSuitesInProcess(ForkedBooter.java:345)
[2024-08-13T21:08:47.974Z] 	at org.apache.maven.surefire.booter.ForkedBooter.execute(ForkedBooter.java:126)
[2024-08-13T21:08:47.974Z] 	at org.apache.maven.surefire.booter.ForkedBooter.main(ForkedBooter.java:418)
[2024-08-13T21:08:47.974Z] Caused by: org.apache.ibatis.exceptions.PersistenceException: 
[2024-08-13T21:08:47.974Z] ### Error querying database.  Cause: java.sql.SQLSyntaxErrorException: ORA-00942: table or view "CAMUNDA"."ACTIVITY_TABLE$" does not exist
[2024-08-13T21:08:47.974Z] 
[2024-08-13T21:08:47.974Z] https://docs.oracle.com/error-help/db/ora-00942/
[2024-08-13T21:08:47.974Z] ### The error may exist in org/camunda/bpm/engine/impl/mapping/entity/TableData.xml
[2024-08-13T21:08:47.974Z] ### The error may involve org.camunda.bpm.engine.impl.TablePageMap.selectTableCount-Inline
[2024-08-13T21:08:47.974Z] ### The error occurred while setting parameters
[2024-08-13T21:08:47.974Z] ### SQL: select count(*) from ACTIVITY_TABLE$
[2024-08-13T21:08:47.974Z] ### Cause: java.sql.SQLSyntaxErrorException: ORA-00942: table or view "CAMUNDA"."ACTIVITY_TABLE$" does not exist
[2024-08-13T21:08:47.974Z] 
[2024-08-13T21:08:47.974Z] https://docs.oracle.com/error-help/db/ora-00942/
[2024-08-13T21:08:47.974Z] 	at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30)
[2024-08-13T21:08:47.974Z] 	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:156)
[2024-08-13T21:08:47.974Z] 	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:147)
[2024-08-13T21:08:47.974Z] 	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:142)
[2024-08-13T21:08:47.974Z] 	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectOne(DefaultSqlSession.java:75)
[2024-08-13T21:08:47.974Z] 	at org.camunda.bpm.engine.impl.db.sql.DbSqlSession.lambda$selectOne$3(DbSqlSession.java:128)
[2024-08-13T21:08:47.975Z] 	at org.camunda.bpm.engine.impl.util.ExceptionUtil.doWithExceptionWrapper(ExceptionUtil.java:334)
[2024-08-13T21:08:47.975Z] 	... 40 more
[2024-08-13T21:08:47.975Z] Caused by: java.sql.SQLSyntaxErrorException: ORA-00942: table or view "CAMUNDA"."ACTIVITY_TABLE$" does not exist
[2024-08-13T21:08:47.975Z] 
[2024-08-13T21:08:47.975Z] https://docs.oracle.com/error-help/db/ora-00942/
[2024-08-13T21:08:47.975Z] 	at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:709)
[2024-08-13T21:08:47.975Z] 	at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:609)
[2024-08-13T21:08:47.975Z] 	at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1347)
[2024-08-13T21:08:47.975Z] 	at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:1100)
[2024-08-13T21:08:47.975Z] 	at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:408)
[2024-08-13T21:08:47.975Z] 	at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:499)
[2024-08-13T21:08:47.975Z] 	at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:274)
[2024-08-13T21:08:47.975Z] 	at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:1231)
[2024-08-13T21:08:47.975Z] 	at oracle.jdbc.driver.OracleStatement.prepareDefineBufferAndExecute(OracleStatement.java:1412)
[2024-08-13T21:08:47.975Z] 	at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1286)
[2024-08-13T21:08:47.975Z] 	at oracle.jdbc.driver.OracleStatement.executeSQLSelect(OracleStatement.java:1843)
[2024-08-13T21:08:47.975Z] 	at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1619)
[2024-08-13T21:08:47.975Z] 	at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3955)
[2024-08-13T21:08:47.975Z] 	at oracle.jdbc.driver.OraclePreparedStatement.doExecute(OraclePreparedStatement.java:4422)
[2024-08-13T21:08:47.975Z] 	at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:4408)
[2024-08-13T21:08:47.975Z] 	at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1015)
[2024-08-13T21:08:47.975Z] 	at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:65)
[2024-08-13T21:08:47.975Z] 	at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:80)
[2024-08-13T21:08:47.975Z] 	at org.apache.ibatis.executor.BatchExecutor.doQuery(BatchExecutor.java:94)
[2024-08-13T21:08:47.976Z] 	at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:336)
[2024-08-13T21:08:47.976Z] 	at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:158)
[2024-08-13T21:08:47.976Z] 	at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:110)
[2024-08-13T21:08:47.976Z] 	at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:90)
[2024-08-13T21:08:47.976Z] 	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:154)
[2024-08-13T21:08:47.976Z] 	... 45 more
[2024-08-13T21:08:47.976Z] Caused by: Error : 942, Position : 21, SQL = select count(*) from ACTIVITY_TABLE$, Original SQL = select count(*) from ACTIVITY_TABLE$, Error Message = ORA-00942: table or view "CAMUNDA"."ACTIVITY_TABLE$" does not exist
[2024-08-13T21:08:47.976Z] 
[2024-08-13T21:08:47.976Z] 	at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:717)
[2024-08-13T21:08:47.976Z] 	... 68 more

Root cause

• Starting with Oracle 21c, there are new materialized views on a table called ACTIVITY_MVIEW$, ACTIVITY_TABLE$, ACTIVITY_CONFIG$, and ACTIVITY_SNAP_META$.
• We use this query mainly for our tests to get all tables and remove the data from them after each test:

  camunda-bpm-platform/engine/src/main/java/org/camunda/bpm/engine/impl/db/sql/DbSqlSession.java

  Line 662 in f1c8c60

  String selectTableNamesFromOracle = "SELECT table_name FROM all_tables WHERE table_name LIKE ?";

  camunda-bpm-platform/engine/src/main/java/org/camunda/bpm/engine/impl/db/sql/DbSqlSession.java

  Line 668 in f1c8c60

  prepStat.setString(1, databaseTablePrefix + "ACT_%");

• This query uses in the LIKE clause ACT_% to find all Camunda tables.
  • In a LIKE clause _ and % are character placeholders (wildcards) while _ is a placeholder for one single character and % for multiple characters.
• The _ is interpreted as a placeholder although this is actually part of Camundas column names.
• The names of the new materialized views in Oracle 23ai match with the ACT_%.

Solution

Fix the query by escaping the _ correctly:
SELECT table_name FROM all_tables WHERE table_name LIKE ACT-_% ESCAPE '-'

Problem 2

Description

The test ConnectionPersistenceExceptionTest fails with the following error:

Expecting:
 <"22000">
to start with:
 <"08">

Root cause

The test ConnectionPersistenceExceptionTest doesn't yield a 08 class SQL state on the new version of the Oracle JDBC driver for Oracle 19 and 23ai.

Solution

Exclude the test for Oracle databases.

[tasso94]

@yanavasileva, I'm asking for a premature review from you here.

The old-engine-oracle-23 stage fails currently since we cannot fix the 7.21.0 SQL statement to query for the table names as I did on the master. See https://ci-pipeline.cambpm.camunda.cloud/blue/organizations/jenkins/7.22%2Fcambpm-ce%2Fcambpm-daily/detail/PR-4522/6/pipeline/

I propose that we disable the stage for 7.22 and enable it again on master once 7.22.0 is released. This way, we can test the old-engine scenario for 7.23.0, but we have no test coverage for 7.22.0.

Alternative ideas: backport the fix on 7.21 and use the patch release for the old-engine test. Brown flag: why should we fix a problem on 7.21 when it only occurs for Oracle 23, which is not supported on 7.21? A user would need to update Camunda from 7.21.0 to 7.22.0 with Oracle 19 and, after the update, migrate to Oracle 23. It is very unlikely users run in production into this issue since Camunda 7 doesn't need to query for database tables to work. We have an API method on ManagementService, but we use it only for testing. Another downside is that community users cannot run the test suite anymore. Maybe we could work around it with Maven profiles.

Do you have any other ideas?