title: Connection to Oracle via TNS is not working
body: <p>I have a Spring Boot app that works smoothly when it connects to an Oracle instance in the classic way:</p>
  
  <pre><code>jdbc:oracle:thin:@&lt;server name&gt;:&lt;port num&gt;/&lt;service name&gt;
  </code></pre>
  
  <p>However, when I turn to a connection via tns through a tnsnames.ora stored in my local drive "C:\ORACLE\Client11g\network\admin" something doesn't work and I get this exception:</p>
  
  <pre><code>Caused by: org.hibernate.service.spi.ServiceException: Unable to create requested service [org.hibernate.engine.jdbc.env.spi.JdbcEnvironment]
      at org.hibernate.service.internal.AbstractServiceRegistryImpl.createService(AbstractServiceRegistryImpl.java:267)
      at org.hibernate.service.internal.AbstractServiceRegistryImpl.initializeService(AbstractServiceRegistryImpl.java:231)
      at org.hibernate.service.internal.AbstractServiceRegistryImpl.getService(AbstractServiceRegistryImpl.java:210)
      at org.hibernate.engine.jdbc.internal.JdbcServicesImpl.configure(JdbcServicesImpl.java:51)
      at org.hibernate.boot.registry.internal.StandardServiceRegistryImpl.configureService(StandardServiceRegistryImpl.java:94)
      at org.hibernate.service.internal.AbstractServiceRegistryImpl.initializeService(AbstractServiceRegistryImpl.java:240)
      at org.hibernate.service.internal.AbstractServiceRegistryImpl.getService(AbstractServiceRegistryImpl.java:210)
      at org.hibernate.boot.model.process.spi.MetadataBuildingProcess.handleTypes(MetadataBuildingProcess.java:352)
      at org.hibernate.boot.model.process.spi.MetadataBuildingProcess.complete(MetadataBuildingProcess.java:111)
      at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.metadata(EntityManagerFactoryBuilderImpl.java:858)
      at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.build(EntityManagerFactoryBuilderImpl.java:885)
      at org.springframework.orm.jpa.vendor.SpringHibernateJpaPersistenceProvider.createContainerEntityManagerFactory(SpringHibernateJpaPersistenceProvider.java:60)
      at org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean.createNativeEntityManagerFactory(LocalContainerEntityManagerFactoryBean.java:353)
      at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.buildNativeEntityManagerFactory(AbstractEntityManagerFactoryBean.java:373)
      at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.afterPropertiesSet(AbstractEntityManagerFactoryBean.java:362)
      at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1642)
      at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1579)
      ... 36 more
  Caused by: org.hibernate.HibernateException: Access to DialectResolutionInfo cannot be null when 'hibernate.dialect' not set
      at org.hibernate.engine.jdbc.dialect.internal.DialectFactoryImpl.determineDialect(DialectFactoryImpl.java:100)
      at org.hibernate.engine.jdbc.dialect.internal.DialectFactoryImpl.buildDialect(DialectFactoryImpl.java:54)
      at org.hibernate.engine.jdbc.env.internal.JdbcEnvironmentInitiator.initiateService(JdbcEnvironmentInitiator.java:137)
      at org.hibernate.engine.jdbc.env.internal.JdbcEnvironmentInitiator.initiateService(JdbcEnvironmentInitiator.java:35)
      at org.hibernate.boot.registry.internal.StandardServiceRegistryImpl.initiateService(StandardServiceRegistryImpl.java:88)
      at org.hibernate.service.internal.AbstractServiceRegistryImpl.createService(AbstractServiceRegistryImpl.java:257)
      ... 52 more
  
  10:37:32,201 ERROR [org.jboss.msc.service.fail] (ServerService Thread Pool -- 381) MSC000001: Failed to start service jboss.undertow.deployment.default-server.default-host./my_service: org.jboss.msc.service.StartException in service jboss.undertow.deployment.default-server.default-host./my_service: java.lang.RuntimeException: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'entityManagerFactory' defined in class path resource [app/persistence/OracleConfiguration.class]: Invocation of init method failed; nested exception is org.hibernate.service.spi.ServiceException: Unable to create requested service [org.hibernate.engine.jdbc.env.spi.JdbcEnvironment]
      at org.wildfly.extension.undertow.deployment.UndertowDeploymentService$1.run(UndertowDeploymentService.java:85)
      at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
      at java.util.concurrent.FutureTask.run(FutureTask.java:266)
      at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
      at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
      at java.lang.Thread.run(Thread.java:745)
      at org.jboss.threads.JBossThread.run(JBossThread.java:320)
  Caused by: java.lang.RuntimeException: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'entityManagerFactory' defined in class path resource [app/persistence/OracleConfiguration.class]: Invocation of init method failed; nested exception is org.hibernate.service.spi.ServiceException: Unable to create requested service [org.hibernate.engine.jdbc.env.spi.JdbcEnvironment]
      at io.undertow.servlet.core.DeploymentManagerImpl.deploy(DeploymentManagerImpl.java:236)
      at org.wildfly.extension.undertow.deployment.UndertowDeploymentService.startContext(UndertowDeploymentService.java:100)
      at org.wildfly.extension.undertow.deployment.UndertowDeploymentService$1.run(UndertowDeploymentService.java:82)
      ... 6 more
  Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'entityManagerFactory' defined in class path resource [app/persistence/OracleConfiguration.class]: Invocation of init method failed; nested exception is org.hibernate.service.spi.ServiceException: Unable to create requested service [org.hibernate.engine.jdbc.env.spi.JdbcEnvironment]
      at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1583)
      at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:553)
      at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:482)
      at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:306)
      at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:230)
      at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:302)
      at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:197)
      at org.springframework.context.support.AbstractApplicationContext.getBean(AbstractApplicationContext.java:1081)
      at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:856)
      at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:542)
      at org.springframework.boot.context.embedded.EmbeddedWebApplicationContext.refresh(EmbeddedWebApplicationContext.java:122)
      at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:761)
      at org.springframework.boot.SpringApplication.refreshContext(SpringApplication.java:371)
      at org.springframework.boot.SpringApplication.run(SpringApplication.java:315)
      at org.springframework.boot.web.support.SpringBootServletInitializer.run(SpringBootServletInitializer.java:151)
      at org.springframework.boot.web.support.SpringBootServletInitializer.createRootApplicationContext(SpringBootServletInitializer.java:131)
      at org.springframework.boot.web.support.SpringBootServletInitializer.onStartup(SpringBootServletInitializer.java:86)
      at org.springframework.web.SpringServletContainerInitializer.onStartup(SpringServletContainerInitializer.java:169)
      at io.undertow.servlet.core.DeploymentManagerImpl$1.call(DeploymentManagerImpl.java:186)
      at io.undertow.servlet.core.DeploymentManagerImpl$1.call(DeploymentManagerImpl.java:171)
      at io.undertow.servlet.core.ServletRequestContextThreadSetupAction$1.call(ServletRequestContextThreadSetupAction.java:42)
      at io.undertow.servlet.core.ContextClassLoaderSetupAction$1.call(ContextClassLoaderSetupAction.java:43)
      at io.undertow.servlet.api.LegacyThreadSetupActionWrapper$1.call(LegacyThreadSetupActionWrapper.java:44)
      at io.undertow.servlet.api.LegacyThreadSetupActionWrapper$1.call(LegacyThreadSetupActionWrapper.java:44)
      at io.undertow.servlet.api.LegacyThreadSetupActionWrapper$1.call(LegacyThreadSetupActionWrapper.java:44)
      at io.undertow.servlet.api.LegacyThreadSetupActionWrapper$1.call(LegacyThreadSetupActionWrapper.java:44)
      at io.undertow.servlet.api.LegacyThreadSetupActionWrapper$1.call(LegacyThreadSetupActionWrapper.java:44)
      at io.undertow.servlet.api.LegacyThreadSetupActionWrapper$1.call(LegacyThreadSetupActionWrapper.java:44)
      at io.undertow.servlet.core.DeploymentManagerImpl.deploy(DeploymentManagerImpl.java:234)
      ... 8 more
  </code></pre>
  
  <p>The following is my application.properties + application-oracleprod.properties files. Keep in mind that this approach works when I use a SID connection as previously said.</p>
  
  <p>application.properties:</p>
  
  <pre><code>spring.profiles.active=oracleprod
  
  spring.jpa.database-platform=org.hibernate.dialect.Oracle10gDialect
  spring.jpa.hibernate.ddl-auto=validate
  hibernate.show_sql=true
  hibernate.hbm2ddl.auto=update
  hibernate.generate_statistics=true
  </code></pre>
  
  <p>application-oracleprod.properties (here the magic should happen due to this property "oracle.net.tns_admin" which sets the "tnsnames.ora" folder plus the link "@MY_SERVICE" which is obviously present in the "tnsnames.ora" file, but I really don't see what Oracle does under the hood):</p>
  
  <pre><code>spring.datasource.driver-class-name=oracle.jdbc.OracleDriver
  
  #/tnsnames.ora
  oracle.net.tns_admin=C:/ORACLE/Client11g/network/admin
  
  oracle.username=my_user
  oracle.password=xyz
  oracle.url=jdbc:oracle:thin:@MY_SERVICE
  </code></pre>
  
  <p>The Configuration class is clean and straightforward:</p>
  
  <pre><code>@Configuration
  @ConfigurationProperties(prefix="oracle" )
  public class OracleConfiguration {
  
      @NotNull
      private String username;
  
      @NotNull
      private String password;
  
      @NotNull
      private String url;
  
      /*@Value("${oracle.net.tns_admin}")
      private String tns;*/
  
      public void setUsername(String username) {
          this.username = username;
      }
  
      public void setPassword(String password) {
          this.password = password;
      }
  
      public void setUrl(String url) {
          this.url = url;
      }
  
      /*public void setTns(String tns) {
          this.tns = tns;
      }*/
  
      @Bean
      DataSource dataSource() throws SQLException {
          OracleDataSource dataSource = new OracleDataSource(); // we use oracle data source API but it could be mysql
          dataSource.setURL(this.url);
          dataSource.setUser(this.username);
          dataSource.setPassword(this.password);
          dataSource.setImplicitCachingEnabled(true);
          dataSource.setFastConnectionFailoverEnabled(true);
          return dataSource;
      }
  
      @Bean
      public LocalContainerEntityManagerFactoryBean entityManagerFactory() {
  
          LocalContainerEntityManagerFactoryBean em = new LocalContainerEntityManagerFactoryBean();
          try {
              em.setDataSource(dataSource());
          } catch (SQLException e) {
              e.printStackTrace();
          }
  
          em.setPackagesToScan(new String[] { "app.persistence.entity" }); // put the name of entity classes package 
  
          JpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
          em.setJpaVendorAdapter(vendorAdapter);
  
          //em.setJpaProperties(additionalProperties()); // this properties are set by "application-oracle.properties" file
  
          return em;
      }
  
      @Bean
      public PlatformTransactionManager transactionManager(EntityManagerFactory emf) {
  
          JpaTransactionManager transactionManager = new JpaTransactionManager();
          transactionManager.setEntityManagerFactory(emf);
          return transactionManager;
      }
  </code></pre>
  
  <p>To summarize, I have followed the approach suggested in the Oracle documentation in combination with Spring Boot configuration files/class with no luck.</p>
  
  <p>I have also tried different path formats:</p>
  
  <pre><code>C:/ORACLE/Client11g/network/admin/tnsnames.ora
  C:\ORACLE\Client11g\network\admin
  C:\\ORACLE\\Client11g\\network\\admin
  </code></pre>
  
  <p>I think I'm doing well because with a SID connection everything is perfect but probably something hidden is missing.</p>
  
  <p><strong>UPDATE</strong></p>
  
  <p>It turned out the db url is not correct, at least for a TNS connection. The thing is we have something like multiple hosts and not only a single one:</p>
  
  <pre><code>    MY_SERVICE =
    (DESCRIPTION_LIST =
      (LOAD_BALANCE = off)
      (FAILOVER = on)
      (DESCRIPTION =
        (CONNECT_TIMEOUT = 5)
        (TRANSPORT_CONNECT_TIMEOUT = 3)
        (RETRY_COUNT = 3)
        (ADDRESS_LIST =
          (LOAD_BALANCE = on)
          (ADDRESS = (PROTOCOL = TCP)(HOST = &lt;ip_1&gt; )(PORT = 1621))
          (ADDRESS = (PROTOCOL = TCP)(HOST = &lt;ip_2&gt; )(PORT = 1621))
        )
        (CONNECT_DATA =
          (SERVICE_NAME = &lt;my_service_name&gt;)
        )
      )
      (DESCRIPTION =
        (CONNECT_TIMEOUT = 5)
        (TRANSPORT_CONNECT_TIMEOUT = 3)
        (RETRY_COUNT = 3)
        (ADDRESS_LIST =
          (LOAD_BALANCE = on)
          (ADDRESS = (PROTOCOL = TCP)(HOST = &lt;ip_3&gt; )(PORT = 1621))
          (ADDRESS = (PROTOCOL = TCP)(HOST = &lt;ip_4&gt; )(PORT = 1621))
        )
        (CONNECT_DATA =
          (SERVICE_NAME = &lt;my_service_name&gt; )
        )
      )
    )
  </code></pre>
  
  <p>And I got:</p>
  
  <pre><code> javax.persistence.PersistenceException: [PersistenceUnit: default] Unable to build Hibernate SessionFactory
      Caused by: java.lang.RuntimeException: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'entityManagerFactory' defined in class path resource [org/springframework/boot/autoconfigure/orm/jpa/HibernateJpaAutoConfiguration.class]: Invocation of init method failed; nested exception is javax.persistence.PersistenceException: [PersistenceUnit: default] Unable to build Hibernate SessionFactory
      Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'entityManagerFactory' defined in class path resource [org/springframework/boot/autoconfigure/orm/jpa/HibernateJpaAutoConfiguration.class]: Invocation of init method failed; nested exception is javax.persistence.PersistenceException: [PersistenceUnit: default] Unable to build Hibernate SessionFactory
      Caused by: javax.persistence.PersistenceException: [PersistenceUnit: default] Unable to build Hibernate SessionFactory
      Caused by: org.hibernate.exception.JDBCConnectionException: Unable to open JDBC Connection for DDL execution
      Caused by: java.sql.SQLRecoverableException: Errore di I/O: Unknown host specified 
      Caused by: oracle.net.ns.NetException: Unknown host specified "
  </code></pre>
  
  <p>Again, I'm a little bit confused how to tell Spring how to read this TNS configuration.</p>
  
  <p>Any idea?</p>