A transparent Spring Boot REST service to expose Oracle Database logic

Sometimes you have an Oracle database which contains a lot of logic and you want to expose specific logic as REST services. There are a variety of ways to do this. The most obvious one to consider might be Oracle REST Data Services. ORDS is quite powerful and for example supports multiple authentication mechanisms. Another option might be using the database embedded PL/SQL gateway This gateway however is deprecated for APEX and difficult to tune (believe me, I know).

Sometimes there are specific requirements which make the above solutions not viable. For example if you have complex custom authentication logic implemented elsewhere which might be difficult to translate to ORDS or the embedded PL/SQL gateway. Although ORDS can run happily inside a container, this is not the case for the PL/SQL gateway. Both are options specific to the Oracle databases and cannot be used for other flavors of databases. If you are looking for a single set of frameworks or products to implement, these solutions will not suffice.

You can consider creating your own custom service in for example Java. The problem here however is that it is often tightly coupled with the implementation. If for example parameters of a database procedure are mapped to Java objects or a translation from a view to JSON takes place in the service, there is often a tight coupling between the database code and the service.

In this blog post I’ll provide a solution for a transparent Spring Boot REST service which forwards everything it receives to the Oracle database for further processing without this tight coupling, only to to a generic database procedure to handle all REST requests. The general flow of the solution is as follows:

• The service receives an HTTP request from a client
• Service translates the HTTP request to an Oracle database REST_REQUEST_TYPE object type
• Service calls the Oracle database over JDBC with this Object
• The database processes the REST_REQUEST_TYPE and creates a REST_RESPONSE_TYPE Object
• The database returns the REST_RESPONSE_TYPE Object to the service
• The service translates the REST_RESPONSE_TYPE Object to an HTTP response
• The HTTP response is returned to the client

How does it work?

What is a REST request? Well… REST is an architectural style. You’re also not talking about SOA or EDA requests are you? We’re talking about HTTP requests in this case but the method can be applied to other protocols like gRPC, JMS, Kafka if you like. This requires some changes to the code though.

First, if you want a transparent solution to forward requests to the database and return responses from the database, we first have to know what a request and a response is.

What is an HTTP request?

You can read on some basics of HTTP requests here. The following picture taken from previously mentioned link gives a nice summary;

An HTTP request consists of; 

• A method. GET, POST, etc.
• An URL
• A list of HTTP headers such as Content-Type, Host, Accept. Security people like these because web-browsers tend to interpret them. See for example the OWASP Secure Headers Project
• A body

What is an HTTP response

The below image has been taken from here.

Generally speaking an HTTP response consists of 

• A status code
• HTTP headers
• A body

So what does the service do? 

It accepts HTTP requests and translates them to Oracle objects which describe the HTTP request. This is then used to call a database procedure over JDBC (protocol translation). Additionally, you can use the service to do things like security, request logging, service result caching, etc.

Oracle database

PL/SQL limitations

PL/SQL has some limitations to deal with. For example, you cannot define object types in package specifications. And you cannot create an associative array (for storing HTTP headers) inside an Oracle Object type. PL/SQL Object types defined in a package specification cannot be transported over JDBC.

How to work around these limitations

In order to deal with these limitations, an Oracle SQL object type is a good choice. See here. In a body of a package, you can then use these types. Also they can be transported over JDBC. The service (of which you can view the code here) calls the procedure with the required parameters.

Java service

JDBC limitations

JDBC in general does not provide specific Oracle database functionality and datatypes. The Oracle JDBC driver in addition also has some limitations (read the FAQ): Oracle JDBC drivers do not support calling arguments or return values of the PL/SQL types TABLE (now known as indexed-by tables), RESULT SET, RECORD, or BOOLEAN. There are currently no plans to change this. Instead people are encouraged to use RefCursor, Oracle Collections and Structured Object Types. I decided to use Object types since they are easy to use in the database and allow nesting. Main challenges in the service was constructing the correct Objects.

How to run this example

Get a database

First of course have a running Oracle database. You can of course use an existing database in your application landscape or for testing purposes install one yourself. If you’re familiar with Vagrant, an easy way to get up and running quickly can be found here. If you’re not familiar with Vagrant, you can also install an Oracle database in a Docker image. For that you have two mechanisms. Build it yourself (see here). Or download it from Oracle’s container registry. If you do not care about having the database isolated from the rest of your system you can also install it outside VirtualBox/Docker. I recommend XE if you want to go this path since the other database versions require more steps to install.

Create a database user

First login as a system user and create a user which is going to contain the dispatcher.

create user testuser identified by Welcome01
grant dba,resource,connect to testuser

Of course in an enterprise environment, you want to be a bit more specific with your grants.

Create the database objects

CREATE OR REPLACE TYPE HTTP_HEADER_TYPE AS OBJECT
(
name VARCHAR2(255),
value VARCHAR2(2014)
);
/
CREATE OR REPLACE TYPE HTTP_HEADERS_TYPE AS TABLE OF HTTP_HEADER_TYPE;
/
CREATE OR REPLACE TYPE REST_REQUEST_TYPE AS OBJECT
(
HTTP_METHOD VARCHAR2(16),
HTTP_URL VARCHAR2(1024),
HTTP_HEADERS HTTP_HEADERS_TYPE,
HTTP_BODY CLOB
);
/
CREATE OR REPLACE TYPE REST_RESPONSE_TYPE AS OBJECT
(
HTTP_STATUSCODE NUMBER,
HTTP_HEADERS HTTP_HEADERS_TYPE,
HTTP_BODY CLOB
);

Create the database package

Below is a minimal example. Of course here you can write your own implementation as long as the specification remains the same your Java code does not require changing.

CREATE OR REPLACE PACKAGE gen_rest AS

    PROCEDURE dispatcher (
        p_request    IN    rest_request_type,
        p_response   OUT   rest_response_type
    );

END gen_rest;
/
CREATE OR REPLACE PACKAGE BODY gen_rest AS

    PROCEDURE dispatcher (
        p_request    IN    rest_request_type,
        p_response   OUT   rest_response_type
    ) AS
        l_httpheader    http_header_type;
        l_httpheaders   http_headers_type := http_headers_type();
    BEGIN
        l_httpheader := http_header_type('Content-Type', 'application/json');
        l_httpheaders.extend;
        l_httpheaders(l_httpheaders.count) := l_httpheader;
        p_response := rest_response_type(200, l_httpheaders, '{"response":"Hello World"}');
    END dispatcher;

END gen_rest;

Download and install the JDBC driver locally

Update 29-9-2019. This step is no longer needed since the JDBC drivers can be downloaded from Maven Central (com.oracle.ojdbc).

• Download the driver from here. 
• Make sure you have Apache Maven installed and present on the path. If you’re familiar with Chocolatey, use: ‘choco install maven’.
• Install the JDBC driver to your local Maven repository
  mvn install:install-file -Dfile=”ojdbc8.jar” -DgroupId=”com.oracle” -DartifactId=ojdbc8 -Dversion=”19.3″ -Dpackaging=jar

Download and run the Java service

You can find the code here. The actual Java code code consists of two classes and a configuration file. The configuration file, application.properties contains information required by the Hikari connection pool to be able to create connections. This is also the file you need to update when the database has a different service name or hostname.

The service itself is a Spring Boot service. After you have downloaded the code you can just run it like any old Spring Boot service.

Go to the folder where the pom.xml is located

mvn clean package
java -jar .\target\RestService-1.0-SNAPSHOT.jar

Now you can open your browser and go to http://localhost:8080/api/v1/blabla (or any URL after /v1/)

Finally

Considerations

This setup has several benefits;

• There is only a single location which has business logic
  Business logic it is located in the database and not in the service. You might argue that is not the location where this logic should be, however in my opinion better in a single location than distributed over two locations. If the current situation is that the database contains the logic, it is often easiest to keep it there. In the long term however, this causes a vendor lock-in.
• A custom service is flexible
  • The Java service is container ready and easily scalable.
  • The Java service is thin/transparent. You know exactly what happens (not much) and it has the potential to be a lot faster than products which provide more functionality which you might not need.
  • The service can be enriched with whatever custom functionality you like. Products such as ORDS and the PL/SQL gateway are often more difficult to extend and you are not allowed to alter the (closed source) products themselves.
• Not so tight coupling between service and database.
  The database code is immutable and only a single version of the service is required. If the messages change which are exchanged (because of changes in the database code), the service does not need to be changed. If the service is build by another team as the database code, these teams do not need to coordinate their planning and releases.

There are of course some drawbacks;

• Some changes still require redeployment of the service
  If the database itself changes, for example gets a new hostname or requires a new JDBC driver to connect to, the service most likely needs to be redeployed. In a container environment however, you can do this with a rolling zero-downtime upgrade.
• Custom code is your own responsibility
  The service is quickly put together custom code which has not proven itself for production use. I can only say: ‘trust me, it works.. (probably) ;)’
  • There has not been extensive testing. I didn’t take the effort of mocking an Oracle database (JDBC, Oracle database with custom objects, procedures) in a test. Sorry about that.
  • Documentation is limited to this blog post and the comments in the code. 
  • There is no software supplier who you can go to to ask for support, report bugs or you can use to avoid the responsibility of having to deal with issues yourselves. 
• Your database developers will create functionality
  You’re completely dependent on your database developers to implement service functionality. This can be a benefit or drawback, dependent on the people you have available.
• This solution is Oracle database specific
  You’re going to use PL/SQL to implement services. It is not easily portable to other databases. If you do not have a specific reason to implement business logic in your database, do not go this way and cleanly split data and logic preferably in different systems.

JSON in the Oracle database

The example service which has been provided offers little functionality. Functionality is of course customer specific. A challenge can be to process a body and formulate a resposponse from the database. A reason for this is that the request and response body might contain JSON. JSON functionality has only recently been introduced in the Oracle database. A few packages/procedures in 12c and a lot more functionality in 18c and 19c. 11g however offers close to nothing. For 11g there are some alternatives to implement JSON. See for example here. Installing APEX is the easiest.This provides the APEX_JSON package which has a lot of functionality. This package is part of the APEX runtime so you do not need to install the entire development environment. An alternative is the open source library PL/JSON here or if you don’t care about breaking license agreements, you can use the following (of course without any warranties or support).

Suggested improvements to the Java service

The sample service is provided as a minimal example. It does not catch errors and create safe error messages from them. This is a security liability since information on the backend systems can arrive at the user of the service. Also of course as indicated, the service is not secured. It might be vulnerable to DOS attacks and anyone who can call the service can access the database procedure. I’ve not looked at tuning the connection pool yet. Of course you should pay attention to the PROCESSES, SESSIONS, OPEN_CURSORS settings and others of the database. Especially if the service receives lots of calls and has a lot of instances. I’ve not looked at behavior at high concurrency. The service could be re-implemented using for example Spring WebFlux and reactive JDBC drivers to make a single instance more scalable. Of course you can consider implementing a service result cache, preferable by using an external cache (to share state over service instances).