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:


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="" xmlns:us01=""
 elementFormDefault="qualified" targetNamespace="">
 <xs:element name="GetNameValuePairRequest">
 <xs:element name="serviceName" type="xs:string"/>
 <xs:element name="inputName" type="xs:string"/>
 <xs:element name="GetNameValuePairResponse">
 <xs:element name="resultValue" type="xs:string"/>

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 = "";
declare namespace xf = "";
declare namespace funcRBX = "";

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:resultValue>{ funcRBX:runSql($getNameValuePairRequest1/ns0:serviceName/text(),$getNameValuePairRequest1/ns0:inputName/text())}</ns0:resultValue>

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


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.