Calling an Oracle DB stored procedure from Spring Boot using Apache Camel

There are different ways to create data services. The choice for a specific technology to use, depends on several factors inside the organisation which wishes to realize these services. In this blog post I'll provide a minimal sample on how you can use Spring Boot with Apache Camel to call an Oracle database procedure which returns the result of an SQL query as an XML. You can browse the code here.

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. It is quite powerful and supports multiple authentication mechanisms like OAuth. 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. ORDS also runs in stand-alone in a Docker container but this is not so easy for the PL/SQL gateway. Also if you are looking for a product or framework which can be used for multiple flavors of database, these solutions might be too Oracle specific.

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 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

Getting started with Oracle Database in a Docker container!

One of the benefits of using Docker is quick and easy provisioning. I wanted to find out first-hand if this could help me get an Oracle Enterprise Edition database quickly up and running for use in a development environment. Oracle provides Docker images for its Standard and Enterprise Edition database in the Oracle Container Registry. Lucas Jellema has already provided two blogs on this (here and here) which have been a useful starting point. In this blog I'll describe some of the choices to make and challenges I encountered. To summarize, I'm quite happy with the Docker images in the registry as they provide a very easy way to automate the install of an EE database. You can find a Vagrant provisioning shell script with the installation of Docker and Docker commands to execute here and a description on how to use it here.