Using execute-sql for Name-Value Pair lookup in Oracle Service Bus

For multiple services in our OSB landscape we needed some sort of name-value pair lookup mechanism. What we didn’t want is excessive IF-THEN-ELSE statements in our OSB services, so in the end we decided to create:

• a function (local type Oracle Service Bus proxy service)
• use a simple database table from where we could externally retrieve the data
• access the database through the fn-bea:execute-sql() function.

So first things first:

Creating the table with SQL where we use (parent proxy) SERVICE and NAME as input to retrieve the required VALUE:

CREATE TABLE NVPTABLE
(
 "SERVICE" VARCHAR2(256 BYTE),
 "NAME" VARCHAR2(256 BYTE),
 "VALUE" VARCHAR2(256 BYTE)
)

REM INSERTING into NVPTABLE
Insert into NVPTABLE (SERVICE,NAME,VALUE) values ('myParentService','010','Rotterdam');
Insert into NVPTABLE (SERVICE,NAME,VALUE) values ('myParentService','020','Amsterdam');
Insert into NVPTABLE (SERVICE,NAME,VALUE) values ('myParentService','030','Utrecht');
Insert into NVPTABLE (SERVICE,NAME,VALUE) values ('myParentService','040','Eindhoven');
Insert into NVPTABLE (SERVICE,NAME,VALUE) values ('myParentService','073','Den Bosch');

In Weblogic we create a datasource, use jdbc/nvp_ds as JNDI name and  target it to the correct OSB managed servers.

Then in our OEPE (or Notepad++) we create the following XML schema and use it in a WSDL as a nice datamodel for our soon-to-be function. As you can see the 2 elements for request and response are simple and logically match the database columns.

<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:us01="http://rubix.nl/schemas/cdm/utility01"
 elementFormDefault="qualified" targetNamespace="http://rubix.nl/schemas/cdm/utility01">
 <xs:element name="GetNameValuePairRequest">
 <xs:complexType>
 <xs:sequence>
 <xs:element name="serviceName" type="xs:string"/>
 <xs:element name="inputName" type="xs:string"/>
 </xs:sequence>
 </xs:complexType>
 </xs:element>
 <xs:element name="GetNameValuePairResponse">
 <xs:complexType>
 <xs:sequence>
 <xs:element name="resultValue" type="xs:string"/>
 </xs:sequence>
 </xs:complexType>
 </xs:element>
</xs:schema>

Then create a local proxy service which uses the SOAP/WSDL. The message flow of our function (local proxy) can be simple and just use 1 XQuery transformation to do all the magic stuff:

And this is how the magic really looks like under the hood:

(:: pragma bea:global-element-parameter parameter="$getNameValuePairRequest1" element="ns0:GetNameValuePairRequest" location="../interfaces/util01.GetNameValuePair.xsd" ::)
(:: pragma bea:global-element-return element="ns0:GetNameValuePairResponse" location="../interfaces/util01.GetNameValuePair.xsd" ::)

declare namespace ns0 = "http://rubix.nl/schemas/cdm/utility01";
declare namespace xf = "http://tempuri.org/Local.GetNameValue/transformation/resultXQ/";
declare namespace funcRBX = "http://www.rubix.nl/local";

declare function funcRBX:runSql($inputA as xs:string, $inputB as xs:string) as xs:string
{
 let $sqlstring := concat('SELECT value FROM NVPTABLE where SERVICE=''',$inputA,''' and NAME=''',$inputB,'''')
 let $result := fn-bea:execute-sql('jdbc/nvp_ds','result',$sqlstring)
 return $result
};

declare function xf:resultXQ($getNameValuePairRequest1 as element(ns0:GetNameValuePairRequest))
 as element(ns0:GetNameValuePairResponse) {
 <ns0:GetNameValuePairResponse>
 <ns0:resultValue>{ funcRBX:runSql($getNameValuePairRequest1/ns0:serviceName/text(),$getNameValuePairRequest1/ns0:inputName/text())}</ns0:resultValue>
 </ns0:GetNameValuePairResponse>
};

declare variable $getNameValuePairRequest1 as element(ns0:GetNameValuePairRequest) external;

xf:resultXQ($getNameValuePairRequest1)

The resultXQ function which does the XQuery transformation uses the custom funcRBX:runSql function (don’t forget to declare the namespace). This runSql function performs the following steps.

• First we concatenate the SQL-statement we want to use to run against the database. We do this to prevent some weird typecasting problems which can occur with the execute-sql function.
• Second we run the function and return the result. Using the let and return command, we prevent the default behaviour of the execute-sql function to return:
  <result><VALUE>Den Bosch</VALUE><result>

Voila, you are finished and the final result will look like this:

Hope it helps.

References:

• how to properly use execute-sql() @ Java Mon Amour