Advertisements
RSS

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:

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:

Advertisements
 
6 Comments

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.
    Thanks!

     
    • 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:

WordPress.com Logo

You are commenting using your WordPress.com 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: