Exposing NULL values and Booleans in JSON/REST with Oracle SOA Suite

Exposing NULL values and Booleans in JSON/REST with Oracle SOA Suite

Johannes Michler PROMATIS Horus Oracle

Executive Vice President – Head of Platforms & Development

Recently we had a couple of projects where we needed to expose data extracted from an Oracle Database through a simple SQL query as a REST service producing JSON. Let’s have a look at how it is possible to control the JSON produced in such a scenario.


The composite is very, very trivial:

Composite in SOA Suite

It just maps two URL query parameters to the WHERE condition of the SQL query:

REST Service Configuration

When the service is invoked, unfortunately, the JSON returned is not really “optimal”:

"XxisBlanketMatkoV" : [ {
"lineId" : 278000,
"shipSiteAddress1" : "PROMATIS software GmbH",
"shipSiteAddress2" : "Pforzheimer Str. 160",
"shipSiteAddress3" : {
"@nil" : "true"
"shipSiteAddress4" : {
"@nil" : "true"
"shipSiteCountry" : "DE",
"shipSitePlz" : "76275",
"bulkFlag" : "N"
} ]

Issues with initial version

This is ugly for two reasons:

  • One usually does not describe NULL values in JSON like that. It would be better to either “omit” the element “shipSiteAddress3” or have them set to “shipSiteAddress3”: null.
  • bulkFlag is a boolean, it would be better to have this as “bulkFlag” : false.

Of course, there is an obvious solution to this: Just add a mediator or even a BPEL process and do an XSLT transformation from the data from the Database Adapter to an explicit schema, and one can map Y/N to true/false over an xsl:if and is additionally able to e.g. omit the NULL elements based on xsl:if.

However, this is “one more thing” to maintain, and if the base SELECT changes, this transformation has to be adapted as well.

Producing better “null” values

Obviously, SOA Suite first extracts the data from the database column SHIP_SITE_ADDRESS4 (which in NULL) to an xml representation of

<shipSiteAddress3 xsi:nil=”true”/> and then translates that attribute into a json child “@nil”=true.

Luckily in the meantime, SOA Suite got a patch 31926382 (which is part of SOA Bundle Patch With that patch, according to 2764402.1, one is able to modify the XSD that defines the XML elements that in the end are converted to JSON as follows:

Modified XML Schema

just add:

xmlns:nxsd="http://xmlns.oracle.com/pcbpel/nxsd" nxsd:version="JSON" nxsd:encoding="UTF-8" nxsd:isNillableSupported="true"

and the @nil will be converted to “proper” null values.

true and false

Unfortunately, pure (Oracle) SQL does not support boolean data types in the database so far. This also makes it a bit difficult to produce true/false when fetching data through the JCA Database adapter of SOA Suite. I managed to fix this by doing the following:

  1. Returning 0 for false and 1 for true in the SQL field of “bulk_flag,
  2. modifying the or-mappings.xml to:
<attribute-mapping xsi:type="direct-mapping"
<field table="XXIS_BLANKET_MATKO_V" name="BULK_FLAG" xsi:type="column"/>

(Thus changing it from java.lang.Decimal to java.lang.Boolean.)

Summary: Prettier JSON

By implementing these two tiny changes:

  • Add nxsd:isNillableSupported=”true” to schema,
  • modify the or-mappings.xml to have <attribute-classification>java.lang.Boolean</attribute-classification> to

The REST service gives us:

"XxisBlanketMatkoV" : [ {
"lineId" : 278000,
"shipSiteAddress1" : "PROMATIS software GmbH",
"shipSiteAddress2" : "Pforzheimer Str. 160",
"shipSiteAddress3" :null,
"shipSiteAddress4" : null,
"shipSiteCountry" : "DE",
"shipSitePlz" : "76275",
"bulkFlag" : false
} ]

Which is way better JSON ?

SOA Suite on Oracle Cloud Infrastructure (OCI)

SOA Suite on Oracle Cloud Infrastructure (OCI)

Johannes Michler PROMATIS Horus Oracle

Senior Vice President – Head of Platforms & Development

Recently we have seen a lot of our customers implementing Oracle SOA Suite on Oracle Cloud Infrastructure - some upgrading from earlier releases of the SOA Suite, others doing a new implementation of an integration platform based on SOA Suite. This blog will have a look at typical customer situations and how we brought them onto a new SOA integration platform on OCI from a high level perspective.

There is also a recent Oracle Video describing SOA on OCI.

New SOA Suite Customer

The situation we saw with this customer was as follows:

The customer had previously implemented hundreds of 1:1 integrations between their On Premise (in various, globally distributed data centers) and Cloud Applications and was searching for an integration platform around an Enterprise Service Bus. The basic idea is to define Canonical Business Objects that can then be exchanged between the different systems more easily than connecting everything individually.

The customer had neither Oracle Database licenses (for the SOA Suite Metadata repository) nor Oracle SOA Suite licenses. Using Oracle Integration Cloud (OIC) was not (yet) an option, mainly due to complex Event Hub patterns.

Given the above prerequisites, the customer had three options to license Oracle SOA Suite:

In consultation with the Oracle SOA Suite product management (many thanks to their great help during this evaluation and the subsequent project), the customer decided to subscribe to the SOA Suite on OCI Marketplace. Although this was very early after Oracle published the images, we faced no major issues and this approach was:

  • much cheaper than buying On Premise SOA and Database licenses to run them in an On Premise or Cloud Data center and
  • brought us a more up to date and longer supported version ( than we would have gotten with SOACS

Customer doing a SOA Suite Lift And Shift

The situation for this customer was different from the previous one:

The Customer has been using SOA Suite for 10+ years to implement all integrations around their central ERP System "Oracle E-Business Suite 12.1.3". They were initially 10g customers who had upgraded to 12.1.3 and then last year to Their hardware and operating system (SUSE Linux 11) were both running out of support. SOA Suite Release 12.1.3 itself will soon also run out of support. The customer runs their E-Business Dev- and Test Environments on OCI and plans to move EBS PROD as well.

Given the above, the customer had evaluated the following options in more detail:

  • Buy new "On Premise" hardware and do a new installation of SOA Suite there
  • Do a "custom" installation of both SOA Suite and a Database 19c for the Metadata Repository on OCI
  • Use the SOA Suite on OCI marketplace image in the Bring Your Own License (BYOL) fashion: https://cloudmarketplace.oracle.com/marketplace/en_US/listing/74792101

Together with the customer, we finally decided to do a "custom" installation of SOA Suite on OCI. Basically the reasons were:

  • With the marketplace images it is not possible to use a custom installed Oracle Database as the metadata repository. However, using DBaaS for OCI also in the BYOL model is significantly more expensive than using just OCI Compute (and e.g. the 19.7 database marketplace image)
  • On Premises, we had two SOA environments installed on a single machine due to a limited number of SOA Suite licenses. This gave us more flexibility/performance than having two separate machines. This setup is not (yet) supported by the marketplace image
  • The marketplace image has a lot of pre-configured setup (e.g. B2B) that did not fit our needs / previous setup


For both customers running SOA Suite on OCI, it helped them to create state-of-the-art integration architectures using SOA Suite on OCI. So far, all instances have performed in a very stable manner at considerably lower costs than if the customers had run Oracle SOA Suite in an On Premises data center.