Custom XPath Functions in OSB 11g to Lookup Shared DVMs

The Problem Statement(s)

I was recently working on a big OSB implementation for one of our clients using the Oracle SOA Suite 11g platform. Interestingly we had to use both OSB and components in the SOA Suite 11g platform to cater to different needs integration and business scenarios. However one common thing that we needed to use across both the components were DVM’s and XREF’s. It would be naive to highlight this as a problem as this is well known and well blogged about. There has been numerous blog posts showing workarounds to implement DVM alike functionality in OSB. However none of them seemed to work for our implementation. To highlight a few reasons, here were our guidelines and the challenges thereof:

  • All DVM’s were deployed as a shared and reusable project into the MDS. Needless to explain why we did this. Once we deploy DVM’s as a shared project to the MDS they become available from the SOA Composer and can be edited/changed at runtime.
  • These DVM’s were to be used both across OSB and BPEL/Mediator to lookup values. Now obviously there is no direct way to access DVM’s deployed as a shared project or within a composite from OSB, so we relied on creating a composite, exposed as a service that will be invoked by the OSB, have a mediator look up the DVM and return the response.
  • This workaround worked for us but with the baggage of hundreds of calls being made between OSB and SOA Suite service just for DVM lookup retrievals.

Well I am sure everyone will have to say that DVM like functionality will be introduced in the future releases of OSB. But what about till then? Also once Oracle releases a new version of OSB, say 12c, do we expect every 11g implementation in the world to migrate over to it.  The question that lead me to ponder over and thereby resulting in this article was “Is it that difficult to reuse the existing DVM XPath functionalities (and the DVM’s that are deployed to the SOA infrastructure) in OSB?”. Turned out it is difficult but with a little effort, I was able to put the pieces together and will explain in this blog, in a step by step way, how was it all done.

Prerequisite(s)

  • Oracle SOA Suite 11gR1 (PS2 and higher)
  • Oracle Service Bus 11gR1(PS2 and higher)

Creating and Deploying the Shared DVM Project

Let me start by creating a very basic composite containing just a DVM (CountryCode.dvm). This is good enough to test all the three different DVM functions available in Oracle SOA Suite 11g. The DVM just store a couple of records of Country names, their Country and Currency codes. The SharedDVMProject is then deployed to the SOA Infrastructure so that the DVM is available in the MDS.

image

Once the resource gets deployed to the SOA infrastructure the easiest way to locate it externally is by accessing it over http. The deployed DVM should be available at http://<host>:<port>/soa-infra/services/<partition>/<compositeName>/CountryCode.dvm. Replace these values of host, port, partition and the composite name with values belonging to your environment and open the address in a browser to see the DVM xml.

image

Oracle SOA Suite 11g supports primarily three ways to look up DVMs. For a full read on working with DVMs refer to the link http://docs.oracle.com/cd/E14571_01/integration.1111/e10224/med_dvm.htm.

The three available XPath DVM functions are

  • dvm:lookupValue(dvmMetadataURI as string, sourceColumnName as string, sourceValue as string, targetColumnName as string, defaultValue as string) as string

The above function is used to return a looked up value of the string by looking up the value for the target column in a domain value map, where the source column contains the given source value. If the target value is not found the default value is returned.

  • dvm:lookupValue(dvmMetadataURI as string, sourceColumnName as string, sourceValue as string, targetColumnName as string, defaultValue as string, (qualifierSourceColumn as string, qualifierSourceValue as string)*)) as string

This function is also used to retrieve a looked up value of the string by looking up the value for the target column in a domain value map, where the source column contains the given source value. If however you dvm contains qualifiers columns then you can pass multiple qualifier columns and their values additionally to the DVM.

  • dvm:lookupValue1M(dvmMetadataURI as string, sourceColumnName as string, sourceValue as string, (targetColumnName as string)? ) as nodeset

This one to many dvm function returns an XML document fragment containing values for multiple target columns of a domain value map, where the value for the source column is equal to the source value. You can provide all the target columns for which the values needs to be retrieved as comma separated string names.

The arguments as explained in the oracle documentation site for these various functions are:

  • dvmMetadataURI – The domain value map URI. Here in our example you could simply pass CountryCode.dvm
  • sourceColumnName – The source column name.
  • sourceValue – The source value (an XPath expression bound to the source document of the XSLT transformation).
  • targetColumnName – The target column name.
  • defaultValue – If the value is not found, then the default value is returned.
  • qualifierSourceColumn: The name of the qualifier column.
  • qualifierSourceValue: The value of the qualifier.
  • targetColumnName – The name of the target columns. At least one column name should be specified. The question mark symbol (?) indicates that you can specify multiple target column names.

Importing the Custom DVM XPath Library into OSB

Having said, this, we would probably like to see a support for all these three dvm functions if we are building a custom XPath library for them in OSB. Well the Custom DVM XPath library that I have created, and that you can use, provide an implementation of all the three functions. However the usage semantics is a little different and I shall explain how each of the function can be used. Before proceeding further, if you want to know how custom XPath libraries can be added to both OSB designtime and runtime refer to these blogs that contain some useful demonstration about it.

https://beatechnologies.wordpress.com/2010/09/08/custom-xpath-in-osb-11g/

http://docs.oracle.com/cd/E14571_01/doc.1111/e15866/custom_xpath.htm

Once you know the basics of how custom XPath functions can be made available to OSB, download the osb-dvm-xpath.jar available from the link here and copy it in <MW_HOME>\Oracle_OSB1\config\xpath-functions folder.

You would also need a .xml (here custom-osb-dvm-xpath.xml) file, as shown below to describe the usage of these functions. The xml file basically contains how would you like to name your function and also which class in the package has a method to implement the corresponding function.

<xpf:xpathFunctions xmlns:xpf="http://www.bea.com/wli/sb/xpath/config">
<xpf:category id="DVM XPath Functions">
<xpf:function>
<xpf:name>lookupDVM</xpf:name>
<xpf:comment>Lookup Value DVM Function based on dvmLocation,source column, source value, target column and target value</xpf:comment>
<xpf:namespaceURI>http://www.com.soatechnologies.blog/osb/custom/xpath/dvm-xpath-functions</xpf:namespaceURI>
<xpf:className>blog.soatechnologies.osb.dvm.DVMXpath</xpf:className>
<xpf:method>java.lang.String lookupValue(java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.lang.String)</xpf:method>
<xpf:isDeterministic>false</xpf:isDeterministic>
<xpf:scope>Pipeline</xpf:scope>
<xpf:scope>SplitJoin</xpf:scope>
</xpf:function>
<xpf:function>
<xpf:name>lookupDVMWQ</xpf:name>
<xpf:comment>Lookup Value DVM Function based on dvmLocation, source column, source value, target column, target value and qualifiers</xpf:comment>
<xpf:namespaceURI>http://www.com.soatechnologies.blog/osb/custom/xpath/dvm-xpath-functions</xpf:namespaceURI>
<xpf:className>blog.soatechnologies.osb.dvm.DVMXpath</xpf:className>
<xpf:method>java.lang.String lookupValueWQ(java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.lang.String,org.apache.xmlbeans.XmlObject)</xpf:method>
<xpf:isDeterministic>false</xpf:isDeterministic>
<xpf:scope>Pipeline</xpf:scope>
<xpf:scope>SplitJoin</xpf:scope>
</xpf:function>
<xpf:function>
<xpf:name>lookupDVM1M</xpf:name>
<xpf:comment>Lookup Value DVM Function based on dvmLocation, source column, source value and multiple target column qualifiers</xpf:comment>
<xpf:namespaceURI>http://www.com.soatechnologies.blog/osb/custom/xpath/dvm-xpath-functions</xpf:namespaceURI>
<xpf:className>blog.soatechnologies.osb.dvm.DVMXpath</xpf:className>
<xpf:method>org.apache.xmlbeans.XmlObject lookupValue1M(java.lang.String, java.lang.String, java.lang.String,org.apache.xmlbeans.XmlObject)</xpf:method>
<xpf:isDeterministic>false</xpf:isDeterministic>
<xpf:scope>Pipeline</xpf:scope>
<xpf:scope>SplitJoin</xpf:scope>
</xpf:function>
</xpf:category>
</xpf:xpathFunctions>

As you can see that the the XML file has three functions, namely lookupDVM, lookupDVWQ, lookupDVM1M that mimic the ones available in Oracle SOA Suite 11g, albeit with a little difference.

The three available XPath DVM functions are

  • dvmG:lookupValue(dvmLoc as string, sourceColumnName as string, sourceValue as string, targetColumnName as string, defaultValue as string) as string

The above function is exactly the same as the one available in Oracle SOA Suite library. It returns the looked up value for the target column.

  • dvmG:lookupValueWQ(dvmLoc as string, sourceColumnName as string, sourceValue as string, targetColumnName as string, defaultValue as string, qualifierArray element(*)) as string

This function enables a dvm lookup by providing the necessary qualifiers, only in this case rather than passing multiple qualifier columns and their values as sting arguments, the function accepts a Document containing a list of qualifiers.

  • dvmG:lookupValue1M(dvmMetadataURI as string, sourceColumnName as string, sourceValue as string, targetColumnArray as element(*) ) as element(*)*This one to many dvm function returns an XML document fragment containing multiple matched values for all the queried columns for a particular source column/value pair. All the target column names should be passed as an element array instead of repeating string arguments instead.

The arguments as explained in the oracle documentation site for these various functions are:

  • dvmLocation– This is similar to the domain value map URI. However since the DVM will not be present in the OSB runtime but instead be deployed to the SOA infrastructure the URI has to be http://<host>:<port>/soa-infra/services/<partition>/<compositeName>/CountryCode.dvm instead of just CountryCode.dvm, i.e the DVM name.
  • sourceColumnName – The source column name.
  • sourceValue – The source value (an XPath expression bound to the source document of the XSLT transformation).
  • targetColumnName – The target column name.
  • defaultValue – If the value is not found, then the default value is returned.
  • qualifierArray : XML Document containing repeating arrays of qualifier name and column similar to the fragment shown here: <qualifiers><qualifierName>CurrencyCode</qualifierName><qualifierValue>GBR</qualifierValue></qualifiers>. However note that you may use any names for the XML elements but the structure needs to similar.
  • targetColumnArray – An XML Document containing a list of repeating of the target columns. For example, the input should be somewhat like <targetColumns><column1>CountryCode</column1><column2>CurrencyCode</column2></targetColumns>. Here also the name of the elements being used can be anything but the structure of the Element array has to be quite like above.

Once the custom-osb-dvm-xpath.xml and osb-dvm-xpath.jar are placed in the <MW_HOME>\Oracle_OSB1\config\xpath-functions start/restart your Eclipse IDE and the OSB server to see the Custom DVM XPath library made available to the OSB functions. The following image also shows a Xquery file dvmLookup.xq that uses the custom library function dvmaG:lookupDVM by making all the arguments required for the function as externally passed values from users.

image

Once done, execute this on the server by right clicking on the file and Run As –> Run on  Server. This brings up the OSB Test console for XQuery Resource Testing. Pass some valid values to look up a particular DVM target column value as shown below to see the response.

image

Similarly create two more XQueries to test the other two functions. Or alternatively, import this soa.osb.dvm.project.jar that has all the XQueries created for you to test. Look for how the qualifiers are passed in the image below while testing the dvmLookupWQ function. The document map is convenient to create as you can pass as many qualifiers name value pairs under the root document.

image

The lookupDVM1M is a bit different from the functions above as it returns document fragment containing multiple target columns for which the values needs to be retrieved all at once. This is useful in cases where you need to retrieve multiple target column values at once. The only trick part is that the target columns should be placed inside repeating elements inside a root level document while being passed as arguments.

image

Now all was good until now as I could use already deployed DVMs from the OSB itself by just a few XPath functions that provide an exact similar functionality as the ones in Oracle SOA Suite. All was good until I further realized that sending a complete URL for the MDS is a pain. For instance when we use the DVM functions in SOA we just send the DVM name as in CountryCode.dvm but to use the corresponding functions in OSB, i had to use :/soa-infra/services///CountryCode.dvm”>http://<host>:<port>/soa-infra/services/<partition>/<compositeName>/CountryCode.dvm instead. This turned out to be another problem as the environments keep on changing, and so do the partitions that these DVMs are deployed to. Also there were a few hundreds of DVMs that were used and hence using the full URL to access the DVM was not accepted and ruled out.

In the next step, I refined the Custom XPath functions to take just the DVM name instead of the entire URL. But the question is how would the OSB runtime locate the DVM’s that are deployed somewhere in the SOA MDS. Well, turns out all I needed to do is write another utility to look for and extract the DVM from the MDS based on the DVM name.

The Custom DVM classes just needed the MDS Database connection information to connect at runtime and look for the DVM metadata to retrieve it. I simply created a db.properties file, something like what is shown below, that has some name value pairs to describe the database connection details. This file can be edited to replace the values with the ones corresponding to your environment and copied to the <MW_HOME>\user_projects\domains\<Domain_Home> folder of your OSB domain.

username=<MDS User Name>
password=<MDS Password>
dbURL=jdbc:oracle:thin:@<DbHost>:<DbPort>:<DbServiceName>
partitionName=<Partition Name>
connName=<Any arbitrary Name for the MDS Connection>

image

So next time when you test any of the above DVM function, you can either specify the complete URL (or the HTTP address where the DVM is located), or just the DVM name only.

image

Feel free to post comments and suggestions, and possible improvements and I will be glad to incorporate them here. If at all you have a similar requirement or are facing problems similar to what I faced and would like to get the binaries of the jar, send me a message. Hope this helps!

Once again, the files used in this example can be downloaded from the links below

osb-dvm-xpath.jar

soa.osb.dvm.project.jar

.

7 thoughts on “Custom XPath Functions in OSB 11g to Lookup Shared DVMs

  1. Arun, I’m trying to import the custom xpath functions into OEPE, but they won’t show up. I copy the jar and the config xml to my $middleware_home/Oracle_OSB1/config/xpath-functions, but when I restart oepe, the functions don’t show up.

    I redirected the output of oepe and there is a classpath error:

    <CustomXPathFunctionManager: Error during initialization! File: custom-osb-dvm-xpath.xml. Error: java.lang.NoClassDefFoundError: oracle/tip/dvm/exception/DVMException
    java.lang.NoClassDefFoundError: oracle/tip/dvm/exception/DVMException

    I see that your jar contains soa-xpath-exts.jar, which contains the DVMException class. To get this working, I needed to copy the lib/jars in your jar to the xpath-functions folder. Did you have to do this?

    Also, are you able to post the source of your functions?

    Thanks

    Like

  2. Instead of burying the lookup in the custom functions why not create a business service that retrieves the DVM and caches it within the Coherence cache? Then you wouldn’t have to make all those round-trip connections across the network. The proxy service’s message flow will then perform the XPath lookup, but only on the XML document returned from the cache. The first time the business service is invoked then the round-trip is made; thereafter, the OSB knows the DVM is within the cache.

    Like

  3. Pingback: Lookup Shared DVM in OSB | Oracle Technologies Premier | jmmate JavaBlog

If you have any comments, suggestions or feedback about the post, please feel free to type it here and I will do my best to address them asap

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