[\/et_pb_text][et_pb_text ul_type=\"square\" _builder_version=\"4.20.0\" _module_preset=\"default\" link_font=\"Open Sans||||on||||\" link_text_color=\"#00A9A0\" ul_font=\"Open Sans||||||||\" ul_font_size=\"17px\" ul_line_height=\"1.6em\" header_font=\"|600||on|||||\" header_text_color=\"gcid-32812186-bc94-4de4-814c-2bf202477fd5\" header_2_font=\"|600||on|||||\" header_2_text_color=\"gcid-32812186-bc94-4de4-814c-2bf202477fd5\" header_2_line_height=\"1.6em\" header_3_font=\"|600|||||||\" header_3_text_color=\"gcid-32812186-bc94-4de4-814c-2bf202477fd5\" header_4_text_color=\"gcid-32812186-bc94-4de4-814c-2bf202477fd5\" header_5_text_color=\"gcid-32812186-bc94-4de4-814c-2bf202477fd5\" header_6_text_color=\"gcid-32812186-bc94-4de4-814c-2bf202477fd5\" header_6_font_size=\"16px\" custom_margin=\"1vh||0px||false|false\" custom_padding=\"||||true|false\" text_font_size_tablet=\"20px\" text_font_size_phone=\"17px\" text_font_size_last_edited=\"on|tablet\" header_font_size_tablet=\"\" header_font_size_phone=\"28px\" header_font_size_last_edited=\"on|phone\" global_colors_info=\"{%22gcid-32812186-bc94-4de4-814c-2bf202477fd5%22:%91%22header_text_color%22,%22header_2_text_color%22,%22header_3_text_color%22,%22header_4_text_color%22,%22header_5_text_color%22,%22header_6_text_color%22%93}\" theme_builder_area=\"post_content\"]<\/p>\n
[\/et_pb_text][et_pb_text ul_type=\"square\" _builder_version=\"4.20.0\" _module_preset=\"default\" text_text_color=\"gcid-0becd5ff-19fc-4653-a221-c8c75771a987\" text_font_size=\"22px\" link_font=\"Open Sans||||on||||\" link_text_color=\"#00A9A0\" ul_font=\"Open Sans||||||||\" ul_font_size=\"17px\" ul_line_height=\"1.6em\" header_font=\"|600||on|||||\" header_text_color=\"gcid-32812186-bc94-4de4-814c-2bf202477fd5\" header_2_font=\"|600||on|||||\" header_2_text_color=\"gcid-32812186-bc94-4de4-814c-2bf202477fd5\" header_2_line_height=\"1.6em\" header_3_font=\"|600|||||||\" header_3_text_color=\"gcid-32812186-bc94-4de4-814c-2bf202477fd5\" header_4_text_color=\"gcid-32812186-bc94-4de4-814c-2bf202477fd5\" header_5_text_color=\"gcid-32812186-bc94-4de4-814c-2bf202477fd5\" header_6_text_color=\"gcid-32812186-bc94-4de4-814c-2bf202477fd5\" header_6_font_size=\"16px\" custom_margin=\"5px||0px||false|false\" custom_padding=\"||||true|false\" text_font_size_tablet=\"20px\" text_font_size_phone=\"17px\" text_font_size_last_edited=\"on|tablet\" header_font_size_tablet=\"\" header_font_size_phone=\"28px\" header_font_size_last_edited=\"on|phone\" global_colors_info=\"{%22gcid-32812186-bc94-4de4-814c-2bf202477fd5%22:%91%22header_text_color%22,%22header_2_text_color%22,%22header_3_text_color%22,%22header_4_text_color%22,%22header_5_text_color%22,%22header_6_text_color%22,%22header_text_color%22,%22header_2_text_color%22,%22header_3_text_color%22,%22header_4_text_color%22,%22header_5_text_color%22,%22header_6_text_color%22,%22header_text_color%22,%22header_2_text_color%22,%22header_3_text_color%22,%22header_4_text_color%22,%22header_5_text_color%22,%22header_6_text_color%22%93,%22gcid-0becd5ff-19fc-4653-a221-c8c75771a987%22:%91%22text_text_color%22%93}\" theme_builder_area=\"post_content\"]<\/i><\/a><\/i><\/a><\/i><\/a>[\/et_pb_text][\/et_pb_column][\/et_pb_row][\/et_pb_section][et_pb_section fb_built=\"1\" _builder_version=\"4.16\" _module_preset=\"default\" custom_padding=\"0vh||10vh||false|false\" global_colors_info=\"{}\" theme_builder_area=\"post_content\"][et_pb_row use_custom_gutter=\"on\" _builder_version=\"4.17.3\" _module_preset=\"default\" custom_padding=\"0px||0px||true|false\" global_colors_info=\"{}\" theme_builder_area=\"post_content\"][et_pb_column type=\"4_4\" _builder_version=\"4.16\" _module_preset=\"default\" global_colors_info=\"{}\" theme_builder_area=\"post_content\"][et_pb_text _builder_version=\"4.20.2\" _module_preset=\"default\" background_enable_color=\"off\" custom_padding=\"0px||0px||true|false\" inline_fonts=\"Times New Roman\" global_colors_info=\"{%22gcid-32812186-bc94-4de4-814c-2bf202477fd5%22:%91%22header_text_color%22,%22header_2_text_color%22,%22header_3_text_color%22%93,%22gcid-0becd5ff-19fc-4653-a221-c8c75771a987%22:%91%22background_color%22%93}\" theme_builder_area=\"post_content\"]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\u2019s have a look at how it is possible to control the JSON produced in such a scenario.<\/p>\n The composite is very, very trivial:<\/p>\n It just maps two URL query parameters to the WHERE condition of the SQL query:<\/p>\n When the service is invoked, unfortunately, the JSON returned is not really \u201coptimal\u201d:<\/p>\n This is ugly for two reasons:<\/p>\n 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.<\/p>\n However, this is \u201cone more thing\u201d to maintain, and if the base SELECT changes, this transformation has to be adapted as well.<\/p>\n Obviously, SOA Suite first extracts the data from the database column SHIP_SITE_ADDRESS4 (which in NULL) to an xml representation of<\/p>\n <shipSiteAddress3 xsi:nil=\u201dtrue\u201d\/> and then translates that attribute into a json child \u201c@nil\u201d=true.<\/p>\n Luckily in the meantime, SOA Suite got a patch 31926382 (which is part of SOA Bundle Patch 12.2.1.4.221122). 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:<\/p>\n just add:<\/p>\n and the @nil will be converted to \u201cproper\u201d null values.<\/p>\n 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:<\/p>\n (Thus changing it from java.lang.Decimal to java.lang.Boolean.)<\/p>\n By implementing these two tiny changes:<\/p>\n The REST service gives us:<\/p>\n Which is way better JSON ?[\/et_pb_text][\/et_pb_column][\/et_pb_row][\/et_pb_section]<\/p>\n","protected":false},"excerpt":{"rendered":" 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\u2019s have a look at how it is possible to control the JSON produced in such a scenario.<\/p>\n","protected":false},"author":2,"featured_media":1235,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_et_pb_use_builder":"on","_et_pb_old_content":"","_et_gb_content_width":"","footnotes":""},"categories":[35],"tags":[103,128],"dipi_cpt_category":[],"class_list":["post-1297","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-techblog-en","tag-oracle-soa-suite","tag-sql-en"],"_links":{"self":[{"href":"https:\/\/promatis-test.de\/at\/en\/wp-json\/wp\/v2\/posts\/1297"}],"collection":[{"href":"https:\/\/promatis-test.de\/at\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/promatis-test.de\/at\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/promatis-test.de\/at\/en\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/promatis-test.de\/at\/en\/wp-json\/wp\/v2\/comments?post=1297"}],"version-history":[{"count":0,"href":"https:\/\/promatis-test.de\/at\/en\/wp-json\/wp\/v2\/posts\/1297\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/promatis-test.de\/at\/en\/wp-json\/wp\/v2\/media\/1235"}],"wp:attachment":[{"href":"https:\/\/promatis-test.de\/at\/en\/wp-json\/wp\/v2\/media?parent=1297"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/promatis-test.de\/at\/en\/wp-json\/wp\/v2\/categories?post=1297"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/promatis-test.de\/at\/en\/wp-json\/wp\/v2\/tags?post=1297"},{"taxonomy":"dipi_cpt_category","embeddable":true,"href":"https:\/\/promatis-test.de\/at\/en\/wp-json\/wp\/v2\/dipi_cpt_category?post=1297"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}![](\"\/.\/wp-content\/uploads\/2023\/02\/ACE-Director-Logo_175x51.jpg\"){kind=logo}
Executive Vice President<\/strong> \u2013\u00a0Head of Platforms\u00a0&\u00a0Development<\/p>\nImplementation<\/h3>\n
![\"\"](\"https:\/\/promatis.com\/wp-content\/uploads\/2023\/03\/composite_soa-suite.png\")
\nComposite in SOA Suite<\/em><\/p>\n![\"\"](\"https:\/\/promatis.com\/wp-content\/uploads\/2023\/03\/rest-service-configuration.png\")
\nREST Service Configuration<\/em><\/p>\n{\r\n\"XxisBlanketMatkoV\" : [ {\r\n\"lineId\" : 278000,\r\n\"shipSiteAddress1\" : \"PROMATIS software GmbH\",\r\n\"shipSiteAddress2\" : \"Pforzheimer Str. 160\",\r\n\"shipSiteAddress3\" : {\r\n\"@nil\" : \"true\"\r\n},\r\n\"shipSiteAddress4\" : {\r\n\"@nil\" : \"true\"\r\n},\r\n\"shipSiteCountry\" : \"DE\",\r\n\"shipSitePlz\" : \"76275\",\r\n\"bulkFlag\" : \"N\"\r\n} ]\r\n}<\/pre>\nIssues with initial version<\/h3>\n
\n
Producing better \u201cnull\u201d values<\/h3>\n
![\"\"](\"https:\/\/promatis.com\/wp-content\/uploads\/2023\/03\/modified-xml-schema.png\")
\nModified XML Schema<\/em><\/p>\nxmlns:nxsd=\"http:\/\/xmlns.oracle.com\/pcbpel\/nxsd\" nxsd:version=\"JSON\" nxsd:encoding=\"UTF-8\" nxsd:isNillableSupported=\"true\"<\/pre>\n
true and false<\/h3>\n
\n
<attribute-mapping xsi:type=\"direct-mapping\"\r\n<attribute-name>bulkFlag<\/attribute-name>\r\n<field table=\"XXIS_BLANKET_MATKO_V\" name=\"BULK_FLAG\" xsi:type=\"column\"\/>\r\n<attribute-classification>java.lang.Boolean<\/attribute-classification>\r\n<\/attribute-mapping>><\/pre>\n
Summary: Prettier JSON<\/h3>\n
\n
{\r\n\"XxisBlanketMatkoV\" : [ {\r\n\"lineId\" : 278000,\r\n\"shipSiteAddress1\" : \"PROMATIS software GmbH\",\r\n\"shipSiteAddress2\" : \"Pforzheimer Str. 160\",\r\n\"shipSiteAddress3\" :null,\r\n\"shipSiteAddress4\" : null,\r\n\"shipSiteCountry\" : \"DE\",\r\n\"shipSitePlz\" : \"76275\",\r\n\"bulkFlag\" : false\r\n} ]\r\n}<\/pre>\n