Tagged ORDS

Microservices and SODA: JSON Data Stored in an Oracle Database Accessed through​ REST or Java

Simple Oracle Document Access

SODA (Simple Oracle Document Access) is a non-SQL style of storing/retrieving JSON data in an Oracle database.

cold summer alcohol cocktail
Photo by Pixabay on Pexels.com

It’s so easy to work with SODA! Let’s get started.

First, enable ORDS schema in your database.

Second, deploy ORDS in K8s (you can also deploy ORDS standalone in your laptop or in a container if you don’t have one, here you can find an example).

Now let’s try it:

Create a collection:

A collection is a table that transparently stores JSON data for you. Execute the following instruction remarked in bold:

curl -i -X PUT

HTTP/1.1 201 Created

Date: Sat, 22 Dec 2018 19:15:26 GMT

X-Frame-Options: SAMEORIGIN

Cache-Control: private,must-revalidate,max-age=0


Content-Length: 0

Insert JSON in the database:

Download this to a file called po.json and execute the following (please note the ID of the new doc created)

curl -X POST --data-binary @po.json -H "Content-Type: application/json" ""


Just in case you haven’t noticed the magic of this, take a look at the following picture:


Retrieve document by ID:

The JSON data is stored in a table. Now we can retrieve the document by the ID that was previously given as follows:

curl -X GET

{"PONumber": 0,

"Reference": "ABANDA-20140803",

"Requestor": "Amit Banda",


"UPCCode": 85391181828},

"Quantity": 9.0}]

Search for documents:

So far, so good. But wait, there is more! Let’s search for the content of a JSON attribute, in this case, documents with PO number is 0:

curl -X POST --data-binary '{"PONumber":"0"}' -H "Content-Type: application/json"

{"items":[{"id":"1C0C85A9B7084DAD88AC9155DC998578","etag":"D17DBB877A3B846EDB32789589F8CDB791634359D615376D938D167AAA3E1F46","lastModified":"2018-12-22T19:21:02.243934Z","created":"2018-12-22T19:21:02.243934Z","value":{"PONumber": 0,...]}}],"hasMore":false,"count":1}

Bulk insert:

Now, let’s insert a bunch of JSON’s, first download this content and save as POlist.json and execute the following in bold:

curl -X POST --data-binary @POlist.json -H "Content-Type: application/json"


As you can observe, we have inserted several objects with one shot.

Update a document:

Let’s update JSON with PO=0 we searched for before (take note of the ID):

curl -i -X PUT --data-binary '{"PONumber" : "99999"}' -H "Content-Type: application/json"

HTTP/1.1 200 OK

Date: Sat, 22 Dec 2018 19:55:10 GMT

X-Frame-Options: SAMEORIGIN

Cache-Control: no-cache,must-revalidate,no-store,max-age=0

ETag: BC9497081F1E314EE19DD777F2ED7E8425F51C21E7EC1968CEA8110A684C3F05

Last-Modified: Sat, 22 Dec 2018 19:55:10 GMT


Content-Length: 0


List records, delete records and so forth:

And so on… Take a look at the SODA REST, Java and whitepaper documentation.

Discover more features

Take a look at the documentation on how to create more complex queries and many other features.

Last, but not least, notice that the JSON object can be different in each row, there is no constraint that forces to a specific structure.

Microservices and SODA

As you probably have noticed, SODA is nice for CRUD operations with JSON data (such as the list of products in the shopping cart) in the database. In addition, you can utilize a relational model for the rest of your app. In brief, SODA is good for building microservices for the modern applications of the digital era.

And finally, by storing your data in an Oracle database, you get benefit of it’s inherent robustness, reliability, resilience, high availability, fault tolerant, distributed (sharding), in-memory, backup/recovery and more.


That’s all folks!

Enjoy 😉



Setup ORDS in a PDB

ORDS utilizes several schemas for working properly. Follow these steps just in case your database hasn’t been set up.

NOTE: you need DBA privileges as well as 2 tablespaces created before proceeding, ask your dba or get this info before starting (although you can also manage it on the fly if you have a few database skills…)


In a machine with java configured and ords.war downloaded to a working directory issue the following command and provide the correct values when prompted:

java -jar ords.war setup
Enter the name of the database server [localhost]: (the IP or name of your database server or scan o vip or whatever such as 13x.x6y.x6y.xyz, ask your dba)
Enter the database listen port [1521]: (this is the default port...ask your dba)
Enter 1 to specify the database service name, or 2 to specify the database SID [1]:
Enter the database service name:(ask your dba, for exmaple orclpdb1.localdomain)
Enter 1 if you want to verify/install Oracle REST Data Services schema or 2 to skip this step [1]:
Enter the database password for ORDS_PUBLIC_USER:(a password provided by you, write it down, you'll need later...)
Confirm password:
Requires SYS AS SYSDBA to verify Oracle REST Data Services schema.

Enter the database password for SYS AS SYSDBA:(you must be a dba or lucky to have been given to you)
Confirm password:
Retrieving information.
Enter the default tablespace for ORDS_METADATA [SYSAUX]:( the name of an existing tblespace such as USERS, ask your dba)
Enter the temporary tablespace for ORDS_METADATA [TEMP]:
Enter the default tablespace for ORDS_PUBLIC_USER [SYSAUX]:USERS
Enter the temporary tablespace for ORDS_PUBLIC_USER [TEMP]:
Enter 1 if you want to use PL/SQL Gateway or 2 to skip this step.
If using Oracle Application Express or migrating from mod_plsql then you must enter 1 [1]:
Enter the PL/SQL Gateway database user name [APEX_PUBLIC_USER]:
Enter the database password for APEX_PUBLIC_USER:
Confirm password:
Enter 1 to specify passwords for Application Express RESTful Services database users (APEX_LISTENER, APEX_REST_PUBLIC_USER) or 2 to skip this step [1]:
Enter the database password for APEX_LISTENER:(a password provided by you, write it down, you'll need later...)
Confirm password:
Enter the database password for APEX_REST_PUBLIC_USER:(a password provided by you, write it down, you'll need later...)
Confirm password:
Dec 22, 2018 12:02:54 PM
INFO: reloaded pools: []
Installing Oracle REST Data Services version 18.3.0.r2701456
... Log file written to /Users/javiermugueta/ords_install_core_2018-12-22_120256_00476.log
... Verified database prerequisites
... Created Oracle REST Data Services schema
... Created Oracle REST Data Services proxy user
... Granted privileges to Oracle REST Data Services
... Created Oracle REST Data Services database objects
... Log file written to /Users/javiermugueta/ords_install_datamodel_2018-12-22_120538_00171.log
... Log file written to /Users/javiermugueta/ords_install_apex_2018-12-22_120551_00321.log
Completed installation for Oracle REST Data Services version 18.3.0.r2701456. Elapsed time: 00:03:08.81

That’s all!


Creating a Java Microservice with Helidon/Microservice Archetype Deployed in Kubernetes


With Helidon you can create Java microservices easily. In this blog, we are creating/exposing a REST service that gets a JSON document stored in an Oracle database and retrieves it to the requestor. For retrieving the JSON document from the database we are using ORDS and SODA but you can use JDBC as well, we’ll show it shortly in another post.

First, let’s create the project with the available archetype:

mvn archetype:generate -DinteractiveMode=false -DarchetypeGroupId=io.helidon.archetypes -DarchetypeArtifactId=helidon-quickstart-se -DarchetypeVersion=0.10.5 -DgroupId=io.helidon.examples -DartifactId=quickstart-se -Dpackage=io.helidon.examples.quickstart.se

Modify the GreetService class for calling a backend service and the MainTest class removing the tests, you can find how it looks like here on GitHub.

Package the app:

mvn clean package

Now, start the app

java -jar target/quickstart-se.jar

And test the app locally, the app starts listening in


Now, let’s containerize the app with the Dockerfile included in the project:

docker build -t quickstart-se target

Run the container and test again:

docker run --rm -p 8080:8080 quickstart-se:latest


Now let’s deploy to Kubernetes:

First, we tag the container and then push it to the registry:

docker tag quickstart-se javiermugueta/quickstart-se

docker push javiermugueta/quickstart-se

Now let’s modify the deployment yaml to create a LoadBalancer and for pulling the image previously pushed in the registry:


app: ${project.artifactId}


type: LoadBalancer




- name: ${proje

image: javiermugueta/quickstart-se

imagePullPolicy: IfNotPresent




Let’s deploy the app to k8s:
kubectl create -f target/app.yaml
kubectl get svc
quickstart-se LoadBalancer 1xy.x1.x5.x24 8080:32243/TCP 2m
Take note of the public IP and test again:
That’s all folks!!

Deploy ORDS (Oracle Rest Data Services) to Kubernetes Cluster


ORDS is a piece of software that acts as an intermediary between Oracle Database and the layers where business logic is implemented and/or consumed.

NOTE: If your database hasn’t been set up for ORDS just google for it or take a look at this post.

With ORDS you expose database objects as REST. ORDS can be started standalone or in an application server. Deploying ORDS in Kubernetes as stateless pods with several replicas provide an elastic layer of rest exposition, out of the database, with high availability and fault tolerant.


First of all, we need a container ready to run ORDS in standalone mode. For this particular case, we are utilising one already done, see yaml file mentioned in next steps.


Let’s start creating a secret for storing the password for the user that makes de connection to the database:

kubectl create secret generic ordspassword --type=string --from-literal=password=[somepassword]
Now, take a look and download this yaml file here and make changes regarding the database connection service and floating IP (SCAN in the case of a RAC or host IP in case of a single instance).
        - name: DB_HOSTNAME
          value: "example:"
        - name: DB_PORT
          value: "example:1521"
        - name: DB_SERVICENAME
          value: "example:jsonpdb.xxxxxx.yyyyyy.oraclevcn.com"
        - name: APEX_PUBLIC_USER_NAME
          value: "ORDS_PUBLIC_USER"
        - name: ORDS_PASS
After that, issue the following command:
kubectl apply -f my.yaml
That’s it, verify the service is available this way:
kubectl get svc
javiermugueta$ kubectl get svc


eciveci LoadBalancer 3000:31522/TCP 4d

kubernetes ClusterIP <none> 443/TCP 15d

ordscontainer LoadBalancer 8080:31523/TCP 2d
Now check whatever URL that you may have enabled in the schema. For more info take a look here or here.
That’s all!
Enjoy 😉