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 example of 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.

Database

How to get an Oracle DB

Oracle provides many options for obtaining an Oracle database. You can use the Oracle Container Registry (here) or use an XE installation (here). I decided to build my own Docker image this time. This provides a nice and quick way to create and remove databases for development purposes. Oracle provides prepared scripts and Dockerfiles for many products including the database, to get up and running quickly.

• git clone https://github.com/oracle/docker-images.git
• cd docker-images/OracleDatabase/SingleInstance/dockerfiles
• Download the file LINUX.X64_193000_db_home.zip from here and place it in the 19.3.0 folder
• Build your Docker image: ./buildDockerImage.sh -e -v 19.3.0
• Create a local folder. for example /home/maarten/dbtmp19c and make sure anyone can read, write, execute to/from/in that folder. The user from the Docker container has a specific userid and by allowing anyone to access it, you avoid problems. This is of course not a secure solution for in production environments! I don’t think you should run an Oracle Database in a Docker container for other then development purposes. Consider licensing and patching requirements.
• Create and run your database. The first time it takes a while to install everything. The next time you start it is up quickly.
  docker run –name oracle19c -p 1522:1521 -p 5500:5500 -e ORACLE_SID=sid -e ORACLE_PDB=pdb -e ORACLE_PWD=Welcome01 -v /home/maarten/dbtmp19c:/opt/oracle/oradata oracle/database:19.3.0-ee
• If you want to get rid of the database instance
  (don’t forget the git repo though)
  docker stop oracle19c
  docker rm oracle19c
  docker rmi oracle/database:19.3.0-ee
  rm -rf /home/maarten/dbtmp19c
  Annnnd it’s gone!

Create a user and stored procedure

Now you can access the database with the following credentials (from your host). For example by using SQLDeveloper.

• Hostname: localhost 
• Port: 1522 
• Service: sid 
• User: system 
• Password: Welcome01

You can create a testuser with

alter session set container = pdb;

-- USER SQL
CREATE USER testuser IDENTIFIED BY Welcome01
DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE "TEMP";

-- ROLES
GRANT "DBA" TO testuser ;
GRANT "CONNECT" TO testuser;
GRANT "RESOURCE" TO testuser;

Login to the testuser user (notice the service is different)

• Hostname: localhost 
• Port: 1522 
• Service: pdb 
• User: testuser 
• Password: Welcome01

Create the following procedure. It returns information of the tables owned by a specified user in XML format.

CREATE OR REPLACE PROCEDURE GET_TABLES 
(
  p_username IN VARCHAR2,RESULT_CLOB OUT CLOB 
) AS
p_query varchar2(1000);
BEGIN
  p_query := 'select * from all_tables where owner='''||p_username||'''';
  select dbms_xmlgen.getxml(p_query) into RESULT_CLOB from dual;
END GET_TABLES;

This is an easy example on how to convert a SELECT statement result to XML in a generic way. If you need to create a specific XML, you can use XMLTRANSFORM or create your XML ‘manually’ with functions like XMLFOREST, XMLAGG, XMLELEMENT, etc.

Data service

In order to create a data service, you need an Oracle JDBC driver to access the database. Luckily, recently, Oracle has put its JDBC driver in Maven central for ease of use. Thank you Kuassi and the other people who have helped making this possible!

        <dependency>
            <groupId>com.oracle.ojdbc</groupId>
            <artifactId>ojdbc8</artifactId>
            <version>19.3.0.0</version>
        </dependency>

The Spring Boot properties which are required to access the database:

• spring.datasource.url=jdbc:oracle:thin:@localhost:1522/pdb
• spring.datasource.driver-class-name=oracle.jdbc.OracleDriver
• spring.datasource.username=testuser
• spring.datasource.password=Welcome01

The part of the code which actually does the call, prepares the request and returns the result is shown below. 

The template for the call is the following:

sql-stored:get_tables('p_username' VARCHAR ${headers.username},OUT CLOB result_clob)?dataSource=dataSource

The datasource is provided by Spring Boot / Spring JDBC / Hikari CP / Oracle JDBC driver. You get that one for free if you include the relevant dependencies and provide configuration. The format of the template is described here. The example illustrates how to get parameters in and how to get them out again. It also shows how to convert a Clob to text and how to set the body to a specific return variable.

Please mind that if the query does not return any results, the OUT variable is Null. Thus getting anything from that object will cause a NullpointerException. Do not use this code as-is! It is only a minimal example

You can look at the complete example here and build it with maven clean package. The resulting JAR can be run with java -jar camel-springboot-oracle-dataservice-0.0.1-SNAPSHOT.jar. 

Calling the service

The REST service is created with the following code:

It responds to a GET call at http://localhost:8081/camel/api/in

Finally

Benefits

Creating data services using Spring Boot with Apache Camel has several benefits:

• Spring and Spring Boot are popular in the Java world. Spring is a very extensive framework providing a lot of functionality ranging from security, monitoring, to implementing REST services and many other things. Spring Boot makes it easy to use Spring.
• There are many components available for Apache Camel which allow integration with diverse systems. If the component you need is not there, or you need specific functionality which is not provided, you can benefit from Apache Camel being open source.
• Spring, Spring Boot and Apache Camel are solid choices which have been worked at for many years by many people and are proven for production use. They both have large communities and many users. A lot of documentation and help is available. You won’t get stuck easily.

There is a good chance that when implementing these 2 together, You won’t need much more for your integration needs. In addition, individual services scale a lot better and usually have a lighter footprint than for example an integration product running on an application server platform.

Considerations

There are some things to consider when to using these products such as;

• Spring / Spring Boot do not (yet) support GraalVMs native compilation out of the box. When running on a cloud environment and memory usage or start-up time matter, you could save money by for example implementing Quarkus or Micronaut. Spring will support GraalVM out of the box in version 5.3 expected Q2 2020 (see here). Quarkus has several Camel extensions available but not the camel-sql extension since that is based on spring-jdbc.
• This example might require specific code per service (depending on your database code). This is custom code you need to maintain and might have overhead (build jobs, Git repositories, etc). You could consider implementing a dispatcher within the database to reduce the amount of required services. See my blog post on this here (consider not using the Oracle object types for simplicity). Then however you would be adhering to the ‘thick database paradigm’ which might not suite your tastes and might cause a vendor lock-in if you start depending on PL/SQL too much. The dispatcher solution is likely not to be portable to other databases.
• For REST services on Oracle databases, implementing Oracle REST Data Services is also a viable and powerful option. Although it can do more, it is most suitable for REST services and only on Oracle databases. If you want to provide SOAP services or are also working with other flavors of databases, you might want to reduce the amount of different technologies used for data services to allow for platform consolidation and make your LCM challenges not harder than they already might be.