I always get oracle.net.jdbc.nl.InvalidSyntaxException twice and in the third attempt the call succeeds

Question

We have a Spring Boot application which utilizes Spring Data to call stored procedures in a Oracle Database. Whenever the application accesses the database, we can see in Dynatrace an exception:

oracle.net.jdbc.nl.InvalidSyntaxException Message: TNS-***** Invalid syntax error: Expected "(" before or at , cn =

This a sample database URL we utilize:

jdbc:oracle:thin:@ldap://mydomain.com:938/DAFPRXHSUSR,cn=OracleContext,dc=eusovd,dc=com

This url follows the oracle connection string syntax for LDAP connections

This exception is thrown twice and then in a third attempt it succeeds to execute the actual call to the database, the thing to notice here is that we didn't code any retry logic, I'm suspecting that

a)the Oracle Driver jar has some retry logic within or that b)maybe the library makes 2 calls before doing the one we coded and that these 2 previous calls are the ones having the issue.

Somehow the driver I think it thinks it's a TNS URL , fails twice and in the 3rd retry it finally gets connected, I don't know if it finally detects it's an LDPAP URL or it reconverts the URL to a TNS format, or if as I mentioned, the driver throws 2 previous calls to the database with wrong URL syntax and then executes the actual call with the right URL syntax

This is the exception stack:

Exception:
oracle.net.jdbc.nl.InvalidSyntaxException
Message:
TNS-***** Invalid syntax error: Expected "(" before or at , cn =
Stacktrace:
oracle.net.jdbc.nl.NVFactory._readTopLevelNVPair(NVFactory.java:97)
oracle.net.jdbc.nl.NVFactory.createNVPair(NVFactory.java:65)
oracle.jdbc.driver.PhysicalConnection.isDRCPConnection(PhysicalConnection.java:11325)
oracle.jdbc.driver.PhysicalConnection.connect(PhysicalConnection.java:1199)
oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:105)
oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:886)
oracle.jdbc.datasource.impl.OracleDataSource.getPhysicalConnection(OracleDataSource.java:707)
oracle.jdbc.datasource.impl.OracleDataSource.getConnection(OracleDataSource.java:381)
oracle.jdbc.datasource.impl.OracleDataSource.getConnectionInternal(OracleDataSource.java:2206)
oracle.jdbc.datasource.impl.OracleDataSource.getConnection(OracleDataSource.java:354)
oracle.jdbc.datasource.impl.OracleDataSource.getConnection(OracleDataSource.java:315)
org.springframework.jdbc.datasource.DataSourceUtils.fetchConnection(DataSourceUtils.java:157)
org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection(DataSourceUtils.java:115)
org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:78)
org.springframework.jdbc.support.JdbcUtils.extractDatabaseMetaData(JdbcUtils.java:319)
org.springframework.jdbc.core.metadata.CallMetaDataProviderFactory.createMetaDataProvider(CallMetaDataProviderFactory.java:74)
org.springframework.jdbc.core.metadata.CallMetaDataContext.initializeMetaData(CallMetaDataContext.java:253)
org.springframework.jdbc.core.simple.AbstractJdbcCall.compileInternal(AbstractJdbcCall.java:314)
org.springframework.jdbc.core.simple.AbstractJdbcCall.compile(AbstractJdbcCall.java:297)
org.springframework.jdbc.core.simple.AbstractJdbcCall.checkCompiled(AbstractJdbcCall.java:356)
org.springframework.jdbc.core.simple.AbstractJdbcCall.doExecute(AbstractJdbcCall.java:383)
org.springframework.jdbc.core.simple.SimpleJdbcCall.execute(SimpleJdbcCall.java:187)
com.esrx.services.inventory.scanner.persistence.repository.impl.InventoryCountsRepositoryImpl.getSnapshotsList(InventoryCountsRepositoryImpl.java:70)
com.esrx.services.inventory.scanner.persistence.repository.impl.InventoryCountsRepositoryImpl$$FastClassBySpringCGLIB$$970dd8c.invoke(<generated>)
org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:749)
org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:139)
org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:688)
com.esrx.services.inventory.scanner.persistence.repository.impl.InventoryCountsRepositoryImpl$$EnhancerBySpringCGLIB$$.getSnapshotsList
com.esrx.services.inventory.scanner.core.service.impl.InventoryCountsServiceImpl.getSnapshotsList(InventoryCountsServiceImpl.java:35)
com.esrx.services.inventory.scanner.web.controller.InventoryCountsController.getSnapshots(InventoryCountsController.java:56)
jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(NativeMethodAccessorImpl.java)
jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

This is one code snippet of one of our calls, the exception is thrown within the SimpleJdbcCall.execute() method:

SimpleJdbcCall jdbcCall = new SimpleJdbcCall(
                    dataSourcePool.getCurrentUserJdbcTemplate())
                    .withoutProcedureColumnMetaDataAccess()
                    .withSchemaName(Constants.SCHEMA_NAME)
                    .withCatalogName(Constants.CATALOG_NAME)
                    .withProcedureName(Constants.ASSIGN_COUNTSHEET_ROW)
                    .declareParameters(new SqlParameter(Constants.P_SNAPSHOT_ID, Types.NUMERIC),
                            new SqlParameter(Constants.P_COUNT_SHEET_NUMBER, Types.NUMERIC),
                            new SqlParameter(Constants.P_SEARCH_WHBIN, 
                            new SqlOutParameter("p_abbrv", Types.VARCHAR));

            SqlParameterSource paramsIn = new MapSqlParameterSource()
                    .addValue(Constants.P_SNAPSHOT_ID, pSnapshotId)
                    .addValue(Constants.P_COUNT_SHEET_NUMBER, pCountSheetNumber)
                    .addValue(Constants.P_SEARCH_WHBIN, searchWhbin);

            Map<String, Object> out = jdbcCall.execute(paramsIn);

This is another code snippet, the difference here is that we also get the exception but only once, and we see that in a second attempt it succeeds. It's important to notice this is the code the application utilizes to create the datasource that is utilized for all database calls:

public OracleDataSource getDataSource(String username, String credential) throws SQLException {

        log.info("..:: --> Create DataSource <-- ::..");
        OracleDataSource dataSource = new OracleDataSource();
        dataSource.setUser(username);
        dataSource.setPassword(credential);
        dataSource.setURL(this.getUrl());

    //  dataSource.getFastConnectionFailoverEnabled();

        // Try to get the connection, if credentials are not valid it will
        // not connect and throw an SQLException
        try(OracleConnection connection = (OracleConnection) dataSource.getConnection()){
            
        }
        

        log.info("Succesful datasource creation for user {}", username);

        return dataSource;
    }

Yes, this OracleDataSource class is not pooled, but that's the application requirement

Java versions tested: 8, 11 Oracle database version: 19.0.0.0.0

These are the Oracle driver versions that have been tested and have the issue (pom.xml):

<dependency>
            <groupId>com.oracle.ojdbc</groupId>
            <artifactId>ojdbc8</artifactId>
            <version>19.3.0.0</version>
            <exclusions>
                <exclusion>
                    <groupId>
                                com.oracle.ojdbc
                            </groupId>
                    <artifactId>simplefan</artifactId>
                </exclusion>
                <exclusion>
                    <groupId>com.oracle.ojdbc</groupId>
                    <artifactId>ons</artifactId>
                </exclusion>
            </exclusions>
        </dependency>

<dependency>
            <groupId>com.oracle.database.jdbc</groupId>
            <artifactId>ojdbc11</artifactId>
            <version>23.5.0.24.07</version>
            <exclusions>
                <exclusion>
                    <groupId>com.oracle.ojdbc</groupId>
                    <artifactId>simplefan</artifactId>
                </exclusion>
                <exclusion>
                    <groupId>com.oracle.ojdbc</groupId>
                    <artifactId>ons</artifactId>
                </exclusion>
            </exclusions>
        </dependency>

I have tried upgrading from java 8 to java 11, algo upgrading the oracle driver from ojdbc8 19.3.0.0 to ojdbc11 23.5.0.24.07, but the problem persists.

Also, I've changed the way we create the datasource replacing OracleDataSource with the pooled datasource HikariDataSource, and the problem is gone! So somehow I think the problem is when utilizing OracleDataSource class, but I can't use the HikariDataSource because we need to login different oracle users to the application with their own Oracle connection (user/password pair), and the HikariDataSource only lets me have one connection pool with one database user. This is the code snippet that utilizes HikariDataSource:

public DataSource getDataSourceV2(String username, String credential) throws SQLException {
        log.info("..:: Create DataSource v2 ::..");
        DataSource ds = DataSourceBuilder.create()
        .username(username)
        .password(credential)
        .url(url)
        .type(HikariDataSource.class)
        .build();
        
        HikariDataSource hds = (HikariDataSource) ds;
        hds.setMaximumPoolSize(1);
        try(Connection conn = ds.getConnection()){
            
        }
        log.info("Succesful datasource creation for user {}", username);
        return ds;
    }