Camunda and Oracle

Hello everyone!
Any help appreciated)

I’m using the following camunda starters:

<dependency>
            <groupId>org.camunda.bpm.springboot</groupId>
            <artifactId>camunda-bpm-spring-boot-starter-webapp</artifactId>
            <version>3.3.1</version>
        </dependency>
        <dependency>
            <groupId>org.camunda.bpm.springboot</groupId>
            <artifactId>camunda-bpm-spring-boot-starter-rest</artifactId>
            <version>3.3.1</version>
        </dependency>

Also :
spring boot 2.1.6.RELEASE
bpm 7.11

Oracle configuration is :

spring.datasource.username=<user>
spring.datasource.password=<password>
camunda.bpm.database.type=oracle
camunda.bpm.database.table-prefix=camunda.
spring.datasource.driverClassName=oracle.jdbc.driver.OracleDriver

When app is run first time (with no camunda related tables) - I got an exception :

### The error may exist in org/camunda/bpm/engine/impl/mapping/entity/Property.xml
### The error may involve org.camunda.bpm.engine.impl.persistence.entity.PropertyEntity.selectProperty-Inline
### The error occurred while setting parameters
### SQL: select * from camunda.ACT_GE_PROPERTY where NAME_ = ?
### Cause: java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

	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.apache.ibatis.session.defaults.DefaultSqlSession.selectOne(DefaultSqlSession.java:77)
	at org.camunda.bpm.engine.impl.db.sql.DbSqlSession.selectById(DbSqlSession.java:110)
	at org.camunda.bpm.engine.impl.db.entitymanager.DbEntityManager.selectById(DbEntityManager.java:213)
	at org.camunda.bpm.engine.impl.persistence.entity.PropertyManager.findPropertyById(PropertyManager.java:29)
	at org.camunda.bpm.engine.impl.HistoryLevelSetupCommand.checkStartupLockExists(HistoryLevelSetupCommand.java:107)
	at org.camunda.bpm.engine.impl.HistoryLevelSetupCommand.execute(HistoryLevelSetupCommand.java:39)
	at org.camunda.bpm.engine.impl.HistoryLevelSetupCommand.execute(HistoryLevelSetupCommand.java:31)
	at org.camunda.bpm.engine.impl.interceptor.CommandExecutorImpl.execute(CommandExecutorImpl.java:28)
	at org.camunda.bpm.engine.impl.interceptor.CommandContextInterceptor.execute(CommandContextInterceptor.java:107)
	... 166 common frames omitted
Caused by: java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

	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)

Although the tables are created after, the second app run gives the following:

Caused by: org.camunda.bpm.engine.ProcessEngineException: ENGINE-03017 Could not perform operation 'create' on database schema for SQL Statement: '-- 
-- 
-- 
-- 
create table ACT_GE_PROPERTY ( 
NAME_ NVARCHAR2(64), 
VALUE_ NVARCHAR2(300), 
REV_ INTEGER, 
primary key (NAME_) 
)'.
	at org.camunda.bpm.engine.impl.db.EnginePersistenceLogger.performDatabaseOperationException(EnginePersistenceLogger.java:231)
	at org.camunda.bpm.engine.impl.db.sql.DbSqlSession.executeSchemaResource(DbSqlSession.java:690)
	at org.camunda.bpm.engine.impl.db.sql.DbSqlSession.executeSchemaResource(DbSqlSession.java:617)
	at org.camunda.bpm.engine.impl.db.sql.DbSqlSession.executeMandatorySchemaResource(DbSqlSession.java:427)
	at org.camunda.bpm.engine.impl.db.sql.DbSqlSession.dbSchemaCreateEngine(DbSqlSession.java:367)
	at org.camunda.bpm.engine.impl.db.AbstractPersistenceSession.dbSchemaUpdate(AbstractPersistenceSession.java:219)
	at org.camunda.bpm.engine.impl.SchemaOperationsProcessEngineBuild.execute(SchemaOperationsProcessEngineBuild.java:56)
	at org.camunda.bpm.engine.impl.SchemaOperationsProcessEngineBuild.execute(SchemaOperationsProcessEngineBuild.java:34)
	at org.camunda.bpm.engine.impl.interceptor.CommandExecutorImpl.execute(CommandExecutorImpl.java:28)
	at org.camunda.bpm.engine.impl.interceptor.CommandContextInterceptor.execute(CommandContextInterceptor.java:107)
	at org.camunda.bpm.engine.spring.SpringTransactionInterceptor$1.doInTransaction(SpringTransactionInterceptor.java:46)
	at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:140)
	at org.camunda.bpm.engine.spring.SpringTransactionInterceptor.execute(SpringTransactionInterceptor.java:44)
	at org.camunda.bpm.engine.impl.interceptor.ProcessApplicationContextInterceptor.execute(ProcessApplicationContextInterceptor.java:70)
	at org.camunda.bpm.engine.impl.interceptor.LogInterceptor.execute(LogInterceptor.java:33)
	at org.camunda.bpm.engine.impl.ProcessEngineImpl.executeSchemaOperations(ProcessEngineImpl.java:120)
	at org.camunda.bpm.engine.impl.ProcessEngineImpl.<init>(ProcessEngineImpl.java:93)
	at org.camunda.bpm.engine.impl.cfg.ProcessEngineConfigurationImpl.buildProcessEngine(ProcessEngineConfigurationImpl.java:798)
	at org.camunda.bpm.engine.spring.SpringTransactionsProcessEngineConfiguration.buildProcessEngine(SpringTransactionsProcessEngineConfiguration.java:63)
	at org.camunda.bpm.engine.spring.ProcessEngineFactoryBean.getObject(ProcessEngineFactoryBean.java:55)
	at org.camunda.bpm.engine.spring.ProcessEngineFactoryBean.getObject(ProcessEngineFactoryBean.java:34)
	at org.springframework.beans.factory.support.FactoryBeanRegistrySupport.doGetObjectFromFactoryBean(FactoryBeanRegistrySupport.java:171)
	... 154 common frames omitted
Caused by: java.sql.SQLSyntaxErrorException: ORA-00955: name is already used by an existing object

	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450)
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399)

Application class is :

@SpringBootApplication
@EnableScheduling
@EnableProcessApplication
public class Application extends SpringBootServletInitializer {

    private static final String BASE_PATH = "/<context_path>";
    
    public static void main(String[] args) {
        SpringApplication.run(Application.class, args);
    }

    /**
     * It is used for Response Header
     *
     * @return
     */
    @Bean
    public Filter filter() {
        ShallowEtagHeaderFilter filter = new ShallowEtagHeaderFilter();
        return filter;
    }

    @Override
    protected SpringApplicationBuilder configure(SpringApplicationBuilder builder) {
        return builder.sources(Application.class);
    }

    @Bean
    public WebServerFactoryCustomizer<ConfigurableServletWebServerFactory>
    webServerFactoryCustomizer() {
        return factory -> factory.setContextPath(BASE_PATH);
    }

}

I tried to use Camunda Spring Boot Starter & Postgres
and Camunda Spring boot oracle, but I also have mentioned starter in my pom :

    <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>

What am I doing wrong?

Any help appreciated.

Thanks a lot

Hello everyone!

Maybe somebody’s got ideas regarding this topic?

Thanks a lot and best regards

Hi @Adriano,

I think your problem is not related to the Camunda engine but to the database setup. Maybe the schema doesn’t fit to the JDBC Url?

Hope this helps, Ingo

Hi @Ingo_Richtsmeier. I removed camunda.bpm.database.table-prefix=camunda.

I do run the app on several oracle schemas and I get only error that tables don’t exist (ORA-00942). Switching from one to another with own application-.properties with own oracle user and password gives the same error. I try to clean the DB before run app - situation is the same.
Should the db schema be absolutely clean (tables, indices and all the related stuff should be removed)? What else can be root cause of the problem?

Current config is :

camunda.bpm.database.type=oracle
camunda.bpm.database.jdbc-batch-processing=false

PS : I tried to use schema-name from here Camunda in separate database schema, but we have dynamically defined oracle schema during the build and before run develper doesn’t know which schema will be used during build

Thanks a lot

Hi @Adriano,

I’m not an Oracle expert. But to figure out any database connection issues, I always use a Java SQL client like SQuirrel and connect to the database from there, to see the correct JDBC URL.

You can create the tables manually as they are included in the engine.jar in the package org.camunda.bpm.engine.db.create.

Hope this helps, Ingo

Hi @Ingo_Richtsmeier

With this configuration

project.oracle.host:<host>
project.oracle.port:<port>
project.oracle.sid:<cid>
spring.datasource.url=jdbc:oracle:thin:@${project.oracle.host}:${project.oracle.port}:${project.oracle.sid}
spring.datasource.platform=oracle
spring.datasource.driver-class-name=oracle.jdbc.OracleDriver
spring.datasource.username=<user>
spring.datasource.password=<password>
camunda.bpm.database.schema-name=${spring.datasource.username}
camunda.bpm.database.table-prefix=${spring.datasource.username}. 

I have successfully switched to other oracle schema and vice versa (seems like without any problems).
Important : camunda.bpm.database.table-prefix property must have “.” char at the end.

So the problem is solved

Thanks a lot