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

24 May

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.



Posted by on 24-05-2012 in Oracle, OSB


Tags: , ,

6 responses to “Using execute-sql for Name-Value Pair lookup in Oracle Service Bus

  1. Pierluigi

    24-05-2012 at 11:02

    lovely article, I will surely use it next time we need to do this kind of lookups

    • jvzoggel

      24-05-2012 at 11:25

      Thank you Pierluigi, for the reply and your blog. It was actually your blogpost about the fn-bea:execute-sql function that inspired me to use a concat for the SQL statement and fix that weird type casting issue I had with the fn-bea:execute-sql function :)

  2. Leonardo Torres Altez

    26-10-2012 at 23:02

    Simple and useful , also you can cache the results using Coherence .

  3. ramiro ortiz

    12-11-2012 at 19:37

    Excellent article.
    I have the following issue, I defined the xquery transformation and I tested it and it’s working fine however I have some problems defining the transformation in the proxy service, Could you please share with me your wsdl file.

    • jvzoggel

      15-11-2012 at 10:37

      You could assing the XQuery transformation in the proxy service by using an Assign (or maybe Replace) action.
      And sorry I don’t have any resources of the project available anymore.

      kind regards, Jan


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: