ORDS, SODA & JSON in the Database

Goal

We want to create a plugin that starts a background task which needs access to a DB connection.

Something like the following code:

import java.io.IOException;
import java.sql.Connection;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import jakarta.inject.Inject;
import oracle.dbtools.plugin.api.di.annotations.Provides;
import oracle.dbtools.plugin.api.http.annotations.Dispatches;
import oracle.dbtools.plugin.api.http.annotations.PathTemplate;
import oracle.dbtools.plugin.api.http.annotations.RequiresPrivilege;
import oracle.dbtools.plugin.api.jdbc.JDBCDataSources;
import oracle.dbtools.plugin.api.servlet.HttpServletBase;

@Provides
@Dispatches(@PathTemplate("/execute"))
@RequiresPrivilege("SOME_PRIVILEGE")
public class ExecutorServlet extends HttpServletBase {

`private static final ExecutorService taskExecutor = Executors.newFixedThreadPool(10);`  

`private final Connection connection;`  
`private final JDBCDataSources dataSources;`  

`@Inject`  
`ExecutorServlet(`  
      `Connection connection,`  
      `JDBCDataSources dataSources`  
`) {`  
   `this.connection = connection;`  
   `this.dataSources = dataSources;`  
`}`  


`@Override`  
`public void doPost(`  
      `HttpServletRequest request,`  
      `HttpServletResponse response`  
`) throws IOException {`  
   `taskExecutor.submit(() -> {`  
      `// we need a db connection here.`  
      `// Cannot use the "connection" field, since that is bound to the request, and we get a “closed” exception when used in the background thread`  
      `// Cannot get it from dataSources, because we don't have a default datasource and don't use hostnames for routing`  
   `});`  
   `response.setStatus(HttpServletResponse.SC_OK);`  
   `response.getWriter().write("Task started");`  
`}`  

}

Setup

ORDS is deployed as a war file in tomcat, with Context path XYZ.

Databases are configured as:

<ords_config>/databases/db1

<ords_config>/databases/db2

<ords_config>/databases/db3

Each db directory contains “wallet”, “pool.xml” (no “hostnames” file, no “paths” file)

ORDS Version 23.2.0

Question

When we call the endpoint as

https://<tomcat-url>:8080/XYZ/db2/execute

How can we acquire a connection to db2 to be used in the taskExecutor background thread?

I have tried

• dataSources.dataSource("db2")

We get a datasource object, but then get an exception:

java.lang.NullPointerException: Cannot invoke "oracle.dbtools.common.pools.DataSourceTarget.getConnection()" because "this.pool" is null
at oracle.dbtools.url.mapping.db.JDBCDataSourceImpl.schemas(JDBCDataSourceImpl.java:71)

• adding a “paths” file with the content db2

Same Exception

• Adding a “hostnames” file

Doesn't really fit our routing needs, because we want to differentiate by the db name in the path