Oracle database 19c - org.springframework.jdbc.CannotGetJdbcConnectionException

[scossin]

We store our CDM data in Oracle database 19c Entreprise Edition. We use Postgresql for WebAPI database to store Oracle connection information. However WebAPI can't open the connection to Oracle because of a missing driver.

We solved the issue by downloading the driver: https://www.oracle.com/database/technologies/jdbc-ucp-122-downloads.html and added it to the pom.xml:

mvn install:install-file -Dfile=./ojdbc8.jar  -DgroupId=com.oracle -DartifactId=ojdbc8 -Dversion=12.2.0.1 -Dpackaging=jar
        <dependency>
            <groupId>com.oracle</groupId>
            <artifactId>ojdbc8</artifactId>
            <version>12.2.0.1</version>
        </dependency>

org.springframework.jdbc.CannotGetJdbcConnectionException: Could not get JDBC Connection; nested exception is java.sql.SQLException: No suitable driver found for jdbc:oracle:thin:@{IP}:{PORT}:{DATABASE}
    at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:80)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:624)
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:688)
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:720)
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:770)
    at org.ohdsi.webapi.report.CDMResultsAnalysisRunner.getDashboard(CDMResultsAnalysisRunner.java:50)
    at org.ohdsi.webapi.service.CDMResultsService.getRawDashboard(CDMResultsService.java:214)
    at org.ohdsi.webapi.service.CDMResultsService.getDashboard(CDMResultsService.java:208)
    at org.ohdsi.webapi.service.CDMResultsService$$FastClassBySpringCGLIB$$f65c71c1.invoke(<generated>)
    at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:736)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
    at org.springframework.cache.jcache.interceptor.JCacheInterceptor$1.invoke(JCacheInterceptor.java:52)
    at org.springframework.cache.jcache.interceptor.JCacheAspectSupport.invokeOperation(JCacheAspectSupport.java:153)
    at org.springframework.cache.jcache.interceptor.JCacheAspectSupport$CacheOperationInvokerAdapter.invoke(JCacheAspectSupport.java:167)
    at org.springframework.cache.jcache.interceptor.CacheResultInterceptor.invoke(CacheResultInterceptor.java:62)
    at org.springframework.cache.jcache.interceptor.JCacheAspectSupport.execute(JCacheAspectSupport.java:123)
    at org.springframework.cache.jcache.interceptor.JCacheAspectSupport.execute(JCacheAspectSupport.java:102)
    at org.springframework.cache.jcache.interceptor.JCacheInterceptor.invoke(JCacheInterceptor.java:61)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:671)
    at org.ohdsi.webapi.service.CDMResultsService$$EnhancerBySpringCGLIB$$a46f1c2.getDashboard(<generated>)
    at org.ohdsi.webapi.cdmresults.DashboardCacheTasklet.execute(DashboardCacheTasklet.java:18)
    at org.springframework.batch.core.step.tasklet.TaskletStep$ChunkTransactionCallback.doInTransaction(TaskletStep.java:406)
    at org.springframework.batch.core.step.tasklet.TaskletStep$ChunkTransactionCallback.doInTransaction(TaskletStep.java:330)
    at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:133)
    at org.springframework.batch.core.step.tasklet.TaskletStep$2.doInChunkContext(TaskletStep.java:272)
    at org.springframework.batch.core.scope.context.StepContextRepeatCallback.doInIteration(StepContextRepeatCallback.java:81)
    at org.springframework.batch.repeat.support.RepeatTemplate.getNextResult(RepeatTemplate.java:374)
    at org.springframework.batch.repeat.support.RepeatTemplate.executeInternal(RepeatTemplate.java:215)
    at org.springframework.batch.repeat.support.RepeatTemplate.iterate(RepeatTemplate.java:144)
    at org.springframework.batch.core.step.tasklet.TaskletStep.doExecute(TaskletStep.java:257)
    at org.springframework.batch.core.step.AbstractStep.execute(AbstractStep.java:200)
    at org.springframework.batch.core.job.SimpleStepHandler.handleStep(SimpleStepHandler.java:148)
    at org.springframework.batch.core.job.AbstractJob.handleStep(AbstractJob.java:392)
    at org.springframework.batch.core.job.SimpleJob.doExecute(SimpleJob.java:135)
    at org.springframework.batch.core.job.AbstractJob.execute(AbstractJob.java:306)
    at org.springframework.batch.core.launch.support.SimpleJobLauncher$1.run(SimpleJobLauncher.java:135)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
    at java.lang.Thread.run(Thread.java:748)
Caused by: java.sql.SQLException: No suitable driver found for jdbc:oracle:thin:@{IP}:{PORT}:{DATABASE}
    at java.sql.DriverManager.getConnection(DriverManager.java:689)
    at java.sql.DriverManager.getConnection(DriverManager.java:208)
    at org.springframework.jdbc.datasource.DriverManagerDataSource.getConnectionFromDriverManager(DriverManagerDataSource.java:153)
    at org.springframework.jdbc.datasource.DriverManagerDataSource.getConnectionFromDriver(DriverManagerDataSource.java:144)
    at org.springframework.jdbc.datasource.AbstractDriverBasedDataSource.getConnectionFromDriver(AbstractDriverBasedDataSource.java:196)
    at org.springframework.jdbc.datasource.AbstractDriverBasedDataSource.getConnection(AbstractDriverBasedDataSource.java:159)
    at org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection(DataSourceUtils.java:111)
    at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:77)
    ... 40 more

[chrisknoll]

It seems oracle drivers are available in maven central repo: https://blogs.oracle.com/developers/your-own-way-oracle-jdbc-drivers-19700-on-maven-central

Here's the helper article on setting your own pom: https://www.oracle.com/database/technologies/maven-central-guide.html#DIY

Would you be willing to try making a local changes to your POM to reference the following artifact:

<dependencyManagement>
  <dependencies>
    <dependency>
      <groupId>com.oracle.database.jdbc</groupId>
       <artifactId>ojdbc-bom</artifactId>
       <version>19.7.0.0</version>
       <type>pom</type>
       <scope>import</scope>
     </dependency>
  </dependencies>
</dependencyManagement>
<dependencies>
  <dependency>
    <groupId>com.oracle.database.jdbc</groupId>
    <artifactId>ojdbc8</artifactId>
   </dependency>
 </dependencies>

That may allow you to resolve the oracle driver dependency without manually registering the driver. Note: I haven't tested if this version of the driver actually will work, but it's from oracle so it should. Note also that the dependencyManagement and dependencies are already defined in the POM.xml in webapi, so these are elements you should add to the existing XML nodes, don't just copy and paste the above into some random location in your pom.xml. On that note: make a backup of your pom before modifying :).

[scossin]

Yes your solution worked: https://github.com/scossin/WebAPI/commit/7c2d3de38228c831bff55754b048237e04c7de97 The pom.xml file we use is here: https://github.com/scossin/WebAPI/blob/master/pom.xml

[anthonysena]

This seems like a great item to contribute to the WebAPI wiki documentation so noting it with a label.

[chrisknoll]

This driver dependency has been added to pom.xml, but under the oracle profile. I think that we want to include oracle drivers by default so that you can add a source for Oracle without building under the oracle-webapi profile. The oracle-webapi profile is for hosting WebAPI db on oracle, which isn't supported...but oracle data sources are.