Wednesday, March 27, 2013

Monitoring Oracle SOA Suite 11g composites using HTTP polling

This post is about efficient vendor neutral monitoring of Oracle SOA composites by providing a mechanism which allows HTTP polling for the state/mode on a per composite basis. First I'll describe what could be the reason to monitor composite process state/mode. Then I'll provide code which can be used to allow HTTP polling of the process state/mode in order to link it to various monitoring tools or custom dashboards.

What is composite state?

There are two properties related to the behavior of a composite in relation to accepting requests and processing already running instances; composite mode and composite state. The mode can be active or retired and the state can be on or off. If the mode is retired, running instances will finish but no new instances can be created. Active means the service is ready to accept requests. If the state is off, the composite is not loaded (shutdown) and as such will not process running requests or pickup new requests. If the state is on, the composite is loaded. The mode and state can be manually changed from Enterprise Manager Fusion Middleware Control. They can also be changed by accessing the API for example as part of an exception handling mechanism.

In the below image you can see the state/mode of all the composites in a partition from Fusion Middleware Control;

In the below image you can see the buttons in Fusion Middleware control to retire/shutdown a composite. Once this is done, buttons for activating/starting the composite become visible.


Why can composite state be important to monitor?

Starting the server

When an Oracle SOA Suite server starts, resources can be pulled in and loaded as part of a composite. Such can be the case when external webservices are called and remote XSD's and WSDL's are used (references). If resources can not be loaded this way, the loading of the entire server might stall. In the Weblogic console however, the server will have the state RUNNING. The SOA Server however will not allow much actions from the webinterface since it is still starting. How to check the SOA serverstate with a servlet is illustrated on http://javaoraclesoa.blogspot.nl/2012/11/soa-suite-cluster-deployments-and.html. If the server is starting, usually the composite which is being started will have state off. Composites which have not been started yet will have state unknown. If the server hangs during start, this can help identifying the problem. Sometimes a manual undeploy is required of a faulty process. This is described on; http://shrikworld.blogspot.nl/2011/04/how-to-undeploy-composite-manually.html. Monitoring the state/mode of individual composites might provide an indication of start-up issues in addition to monitoring the entire SOA Server state.

Exception handling

When using certain methods of exception handling in Oracle SOA Suite 11g, processes can get the retired state in order to stop processing messages after a fault situation. For example, when using the method described on; http://javaoraclesoa.blogspot.nl/2012/06/exception-handling-fault-management-and.html. Usually an error event is triggered linked to sending an e-mail to the person responsible for solving the problem. This person however can take some time before taking action or checking/responding to his mail. In the mean time, the number of requests which need to be processed can increase, requiring more time after activating the composite again before the system is capable of processing new requests. Sometimes this mechanism might be sufficient but a little redundancy in informing people doesn't hurt.

Common monitoring tools

Organizations (especially the larger ones) often use monitoring tooling to determine the state of their server park. If a critical system fails, these monitoring tools immediately inform the person responsible for maintaining server stability, even at night or in weekends. This avoids the problem which can arise when the mail indicating a problem is overlooked. Also usually these monitoring tools have dashboards at which more people look, so the chance the problem is solved quickly increases.

Integrating the raising of the error event from a composite with specific monitoring tooling causes a direct dependency (tight coupling) between the monitoring tooling and the composite error handling mechanism. Also it will often require different disciplines/departments to achieve. Because of this, it is often not advisable to do.

The monitoring tools are not always from the same software vendor as the system which need to be monitored and thus specific components and states/modes are usually not fully supported. Certain common vendor neutral monitoring mechanisms are often used to allow monitoring of diverse systems. Usually these tools allow HTTP polling mechanisms. For example HP SiteScope (http://www8.hp.com/us/en/software-solutions/software.html?compURI=1174244#.UVKxsleyJh4).

Monitoring composite state by using HTTP polling

To monitor composite state via a HTTP polling mechanism, a HttpServlet can be deployed on the Oracle SOA server. The servlet provided in this post accepts an HTTP POST or GET request with the following parameters; name, partition, revision.

It then selects the composite and returns it's state in the response. It's code is based on the previously mentioned sample on; http://javaoraclesoa.blogspot.nl/2012/11/soa-suite-cluster-deployments-and.html. There the code is also explained.

You can test the process after deployment like (where of course hostname needs to be replaced and you should refer to a  composite which is present in your environment);
http://soabpm-vm:7001/DetermineBPELProcessStatus/determinebpelprocessstatus?name=HelloWorld&revision=1.0&partition=default

The output in my case is State: on, Mode: active

To test it's function, you can retire the composite and confirm the servlet returns; State: on, Mode: retired. You can also check the presence of a composite. If you provide a name/revision/partition which is not a valid composite, the State and Mode field will remain empty.

You can download the code here; https://dl.dropbox.com/u/6693935/blog/DetermineCompositeStatus.zip

Saturday, March 16, 2013

Generating Oracle SOA configuration plans; XML manipulation in Ant using XMLTask

Configuration plans can be used for making Oracle SOA deployments specific to an environment. Writing deployment plans can be cumbersome. Especially replacing endpoints of every called service for a newly generated process for every environment is repetitive work and error prone. Also updating the configuration plan when changes occur is often forgotten. A solution for this can be to use a generic configuration plan such as described on; http://javaoraclesoa.blogspot.nl/2013/01/generic-configuration-plan-for-soa.html. This generic plan however is very generic and it will replace endpoints in all files in the project. This might not be what you want. In this post I describe another solution. Generating a configuration plan using the Oracle supplied Ant scripts and then using an Ant script to rewrite it to become specific based on a simple configuration file. This also illustrates how XML manipulations can be done by using xmltask (http://www.oopsconsultancy.com/software/xmltask/) in Ant.

The below explanation of how I've created this might seem complicated. If you just want the tool, you can download a complete working example (which requires little configuration) here; https://dl.dropbox.com/u/6693935/blog/GenConfigPlan.zip. All you have to do to get this working is replace the ORACLE_HOME variable, create a build.properties file for your environments and you're ready to go.

Implementation

build.properties

I've used a build.properties as follows;

envs=dev,prd
replacesets=first,second
first.dev.url=http://192.168.1.1:7001
first.prd.url=http://192.168.1.2:7001
second.dev.url=http://192.168.2.1:8080
second.prd.url=http://192.168.2.2:8080


This properties file specifies both my environments, development (dev) and production (prd). In my environment I have two sets of endpoints to be replaced. My first set (called conveniently 'first') and my second set (second). 'first' can represent for example the Oracle SOA server URL to be replaced and second can be an external service which has references which differ across the environments and thus for which URL's also need to be replaced.

Batch script to start Ant

I've used the following batch script to start the Ant script;

REM Location of Middleware home
set ORACLE_HOME=D:\Oracle\Middleware11116
set ANT_HOME=%ORACLE_HOME%\jdeveloper\ant
set PATH=%ANT_HOME%\bin;%PATH%
set JAVA_HOME=%ORACLE_HOME%\jdk160_24
set CURRENT_FOLDER=%CD%
ant -f genConfigPlan.xml -Dbasedir=%ORACLE_HOME%\jdeveloper\bin -DcompositeDir=%1


The parameter compositeDir specifies the path below which the composite.xml exists and where the new configuration plans need to be created

The batch file can for example be called like;

genConfigPlan.bat D:\dev\HelloWorld\HelloWorldCaller

Where HelloWorldCaller directory is the location of the project which contains the composite.xml. This example is also used in the 'Results' section

Ant script

I've created an Ant build file. This calls the ant-sca-compile script (http://docs.oracle.com/cd/E14571_01/integration.1111/e10224/sca_lifecycle.htm) to generate a default configuration plan. Then I use properties from my configuration file and do replacements for every environment in the generated configuration plan.

Below is the complete Ant script I've used. I will explain the most important parts.

<?xml version="1.0" encoding="iso-8859-1"?>
<project name="soaGenConfigPlan" default="build">
    <property environment="env"/>
   
    <property file="${env.CURRENT_FOLDER}/build.properties"/>
    <taskdef name="xmltask" classname="com.oopsconsultancy.xmltask.ant.XmlTask">
        <classpath>
            <pathelement path="${env.CURRENT_FOLDER}/lib/xmltask.jar"/>
            <pathelement path="${env.CURRENT_FOLDER}/lib/xalan.jar"/>
        </classpath>
    </taskdef>

    <taskdef resource="net/sf/antcontrib/antcontrib.properties">
        <classpath>
            <pathelement location="${env.ORACLE_HOME}/modules/net.sf.antcontrib_1.1.0.0_1-0b2/lib/ant-contrib.jar"/>
        </classpath>
    </taskdef>

    <import file="${basedir}/ant-sca-compile.xml"/>

    <target name="genenvconfigplan">
       
        <echo>Build environment: ${buildenv}</echo>
        <property name="configplan_out" value="${compositename}_cfgplan_${buildenv}.xml"/>
        <echo>Target configplan name: ${configplan_out}</echo>
        <copy file="${compositeDir}/plantemplate.xml" tofile="${compositeDir}/${configplan_out}"/>
        <foreach list="${replacesets}"  target="replaceset" param="replaceset" inheritall="true" inheritrefs="false" delimiter=","/>
    </target>
   
    <target name="replaceset">
        <echo message="Processing replacementset: ${replaceset}"/>
        <propertycopy name="devurl" from="${replaceset}.dev.url"/>
        <propertycopy name="replacewithurl" from="${replaceset}.${buildenv}.url"/>
        <echo message="dev URL: ${devurl}"/>
        <echo message="${buildenv} URL: ${replacewithurl}"/>
        <xmltask source="${compositeDir}/${configplan_out}" destbuffer="myMsg"/>
       
        <xmltask sourcebuffer="myMsg" destBuffer="myMsg">
           
            <insert path="/*[local-name() = 'SOAConfigPlan']/*[local-name() = 'composite']/*[local-name() = 'import']/*[local-name() = 'searchReplace' and position()=1]" position="after"><![CDATA[<searchReplace xmlns="http://schemas.oracle.com/soa/configplan"><search>${devurl}</search><replace>${replacewithurl}</replace></searchReplace>]]></insert>
            <remove path="/*[local-name() = 'SOAConfigPlan']/*[local-name() = 'composite']/*[local-name() = 'import']/*[local-name() = 'searchReplace' and string-length(*[local-name() = 'search']/text())=0]"/>
            <insert path="/*[local-name() = 'SOAConfigPlan']/*[local-name() = 'wsdlAndSchema']/*[local-name() = 'searchReplace' and position()=1]" position="after"><![CDATA[<searchReplace xmlns="http://schemas.oracle.com/soa/configplan"><search>${devurl}</search><replace>${replacewithurl}</replace></searchReplace>]]></insert>
            <remove path="/*[local-name() = 'SOAConfigPlan']/*[local-name() = 'wsdlAndSchema']/*[local-name() = 'searchReplace' and string-length(*[local-name() = 'search']/text())=0]"/>
            <copy path="/*[local-name() = 'SOAConfigPlan']/*[local-name() = 'composite']" buffer="myMsgTmp"/>
            <call path="/*[local-name() = 'SOAConfigPlan']/*[local-name() = 'composite']/*[local-name() = 'reference']">
              <param name="name" path="@name"/>
              <actions>
                <echo>Found a reference: @{name}</echo>
                <xmltask sourcebuffer="myMsgTmp" destbuffer="myMsgTmp">
                <regexp path="/*[local-name() = 'composite']/*[local-name() = 'reference' and @name='@{name}']/*[local-name() = 'binding' and @type='ws']/*[local-name() = 'attribute' and @name='location']/*[local-name() = 'replace']/text()" pattern="(.*)${devurl}(.*)" replace="$1${replacewithurl}$2"/>
                <regexp path="/*[local-name() = 'composite']/*[local-name() = 'reference' and @name='@{name}']/*[local-name() = 'binding' and @type='ws']/*[local-name() = 'property' and @name='endpointURI']/*[local-name() = 'replace']/text()" pattern="(.*)${devurl}(.*)" replace="$1${replacewithurl}$2"/>
                </xmltask>
              </actions>
            </call>
            <replace path="/*[local-name() = 'SOAConfigPlan']/*[local-name() = 'composite']" withBuffer="myMsgTmp"/>
        </xmltask>
        <xmltask sourcebuffer='myMsg' dest="${compositeDir}/${configplan_out}"/>
    </target>

    <target name="build">
    <echo>current folder ${env.CURRENT_FOLDER}</echo>

    <property file="${env.CURRENT_FOLDER}/build.properties"/>
    <input message="Please enter composite directory:" addproperty="compositeDir"/>
    <antcall target="generateplan">
        <param name="scac.input" value="${compositeDir}/composite.xml"/>
        <param name="scac.plan" value="${compositeDir}/plantemplate.xml"/>
    </antcall>
    <xmltask source="${compositeDir}/composite.xml">
        <copy path="/*[local-name() = 'composite']/@name" property="compositename" attrValue="true"/>
    </xmltask>
    <echo message="Composite name: ${compositename}"/>
   
    <foreach list="${envs}" param="buildenv" target="genenvconfigplan" inheritall="true" inheritrefs="true" delimiter=","/>
    <delete file="${compositeDir}/plantemplate.xml"/>

    </target> 
</project>


The default target is 'build'. First it generates a default configuration plan (plantemplate.xml in the below sample) based on the composite.xml from a supplied location;

    <antcall target="generateplan">
        <param name="scac.input" value="${compositeDir}/composite.xml"/>
        <param name="scac.plan" value="${compositeDir}/plantemplate.xml"/>
    </antcall>


Then for every environment (from the properties file build.properties). It calls the target 'genenvconfigplan'. This target generates a configuration plan specific to an environment. This target calls for every replaceset from the build.properties the target 'replaceset'. This allows multiple sets of URL's to be replaced. This target performs the actual replacement by using the xmltask.

xmltask

The top part of the Ant build file is for expanding the classpath in order to include the xmltask Java libraries (see: http://stackoverflow.com/questions/11633308/xmltask-in-java-1-7). Also the Ant task is made known to the script.

    <taskdef name="xmltask" classname="com.oopsconsultancy.xmltask.ant.XmlTask">
        <classpath>
            <pathelement path="${env.CURRENT_FOLDER}/lib/xmltask.jar"/>
            <pathelement path="${env.CURRENT_FOLDER}/lib/xalan.jar"/>
        </classpath>
    </taskdef>


First it inserts a new entry at /SOAConfigPlan/wsdlAndSchema/searchReplace and removes the old empty one. Then it does the same for /SOAConfigPlan/wsdlAndSchema/searchReplace.

           <insert path="/*[local-name() = 'SOAConfigPlan']/*[local-name() = 'composite']/*[local-name() = 'import']/*[local-name() = 'searchReplace' and last()]" position="after"><![CDATA[<searchReplace xmlns="http://schemas.oracle.com/soa/configplan"><search>${devurl}</search><replace>${replacewithurl}</replace></searchReplace>]]></insert>
            <remove path="/*[local-name() = 'SOAConfigPlan']/*[local-name() = 'composite']/*[local-name() = 'import']/*[local-name() = 'searchReplace' and string-length(*[local-name() = 'search']/text())=0]"/>

 
Because of namespace issues I use the local-name() XPATH function to get to the correct path. See for example; http://stackoverflow.com/questions/9381512/xmlpath-from-ant-using-xmltasks-cant-match-if-xml-file-has-elements-in-differen. When I use the 'insert' action and specify a namespace which is a default namespace in the target, the namespace reference get's removed in the resulting XML which is nice.

Replacing the location attribute and endpointURI property in the reference part of the configuration plan was harder. I needed a loop construction here so I could process every reference entry individually. The method I found to achieve this was by using the xmltask 'call' action. Properties in Ant can be set only once, which makes working with them somewhat difficult. xmltask provides an alternative; buffers. Here I encountered some difficulties to process parts of the message. When using xmltask call action, the buffer used inside a called Ant target will be out of scope in the parent process. When using an embedded <actions/> section, the buffer won't be out of scope! So I used the embedded <actions/> section to achieve successful replacement. I've also used a parameter (@{name} in the below sniplet) to be able to select the correct reference node to do the replacement in.

             <call path="/*[local-name() = 'SOAConfigPlan']/*[local-name() = 'composite']/*[local-name() = 'reference']">
              <param name="name" path="@name"/>
              <actions>
                <echo>Found a reference: @{name}</echo>
                <xmltask sourcebuffer="myMsgTmp" destbuffer="myMsgTmp">
                <regexp path="/*[local-name() = 'composite']/*[local-name() = 'reference' and @name='@{name}']/*[local-name() = 'binding' and @type='ws']/*[local-name() = 'attribute' and @name='location']/*[local-name() = 'replace']/text()" pattern="(.*)${devurl}(.*)" replace="$1${replacewithurl}$2"/>
                <regexp path="/*[local-name() = 'composite']/*[local-name() = 'reference' and @name='@{name}']/*[local-name() = 'binding' and @type='ws']/*[local-name() = 'property' and @name='endpointURI']/*[local-name() = 'replace']/text()" pattern="(.*)${devurl}(.*)" replace="$1${replacewithurl}$2"/>
                </xmltask>
              </actions>
            </call>


As can be seen in the above sample I used the regular expression call to do the actual replacement. This way URL's like http://192.168.2.1:8080/webapp/services/mysuperservice would also get replaced properly.

The complete code can be downloaded here; https://dl.dropbox.com/u/6693935/blog/GenConfigPlan.zip

Result

In the below sniplets I've removed the generated comments for brevity. I've created a synchronous hello world process (HelloWorld) and  a synchronous process to call this hello world process (HelloWorldCaller). The default generated configuration plan for HelloWorldCaller is as follows;

<?xml version="1.0" encoding="UTF-8"?>
<SOAConfigPlan xmlns:jca="http://platform.integration.oracle/blocks/adapter/fw/metadata" xmlns:wsp="http://schemas.xmlsoap.org/ws/2004/09/policy" xmlns:orawsp="http://schemas.oracle.com/ws/2006/01/policy" xmlns:edl="http://schemas.oracle.com/events/edl" xmlns="http://schemas.oracle.com/soa/configplan">
   <composite name="HelloWorldCaller">
      <import>
         <searchReplace>
            <search/>
            <replace/>
         </searchReplace>
      </import>
      <service name="helloworldcaller_client_ep">
         <binding type="ws">
            <attribute name="port">
               <replace>http://xmlns.oracle.com/HelloWorld/HelloWorldCaller/HelloWorldCaller#wsdl.endpoint(helloworldcaller_client_ep/HelloWorldCaller_pt)</replace>
            </attribute>
         </binding>
      </service>
      <component name="HelloWorldCaller">
         <property name="bpel.config.transaction">
            <replace>required</replace>
         </property>
      </component>
      <reference name="Service1">
         <binding type="ws">
            <attribute name="port">
               <replace>http://xmlns.oracle.com/HelloWorld/HelloWorld/HelloWorld#wsdl.endpoint(helloworld_client_ep/HelloWorld_pt)</replace>
            </attribute>
            <attribute name="location">
               <replace>http://
192.168.1.1:7001/soa-infra/services/default/HelloWorld/helloworld_client_ep?WSDL</replace>
            </attribute>
            <property name="weblogic.wsee.wsat.transaction.flowOption">
               <replace>WSDLDriven</replace>
            </property>
         </binding>
      </reference>
   </composite>
   <wsdlAndSchema name="HelloWorld.wsdl|HelloWorldCaller.wsdl|xsd/HelloWorld.xsd|xsd/HelloWorldCaller.xsd">
      <searchReplace>
         <search/>
         <replace/>
      </searchReplace>
   </wsdlAndSchema>
</SOAConfigPlan>


This plan is not directly usuable since nothing is replaced. To avoid manually creating a configuration plan for my production environment, I've used the Ant script explained in this post with the mentioned build.properties.

In the below script output you can see all replacement sets are used (first and second) for all environments (dev and prd) and all references (1 in this case).

----------------------------------------------------
Buildfile: genConfigPlan.xml

build:
     [echo] current folder D:\dev\GenConfigPlan
    [input] skipping input as property compositeDir has already been set.

generateplan:
    [input] skipping input as property scac.input has already been set.
    [input] skipping input as property scac.plan has already been set.
[generateplan] Loading Composite file d:\dev\HelloWorld\HelloWorldCaller\composite.xml
[generateplan] Composite loaded
[generateplan] Done generation of soa config plan.
[generateplan] Write soa config plan to file d:\dev\HelloWorld\HelloWorldCaller/plantemplate.xml
[generateplan] Generate plan successful
     [echo] Composite name: HelloWorldCaller

genenvconfigplan:
     [echo] Build environment: dev
     [echo] Target configplan name: HelloWorldCaller_cfgplan_dev.xml
     [copy] Copying 1 file to d:\dev\HelloWorld\HelloWorldCaller

replaceset:
     [echo] Processing replacementset: first
     [echo] dev URL: http://192.168.1.1:7001
     [echo] dev URL: http://192.168.1.1:7001
     [echo] Found a reference: Service1

replaceset:
     [echo] Processing replacementset: second
     [echo] dev URL: http://192.168.2.1:8080
     [echo] dev URL: http://192.168.2.1:8080
     [echo] Found a reference: Service1

genenvconfigplan:
     [echo] Build environment: prd
     [echo] Target configplan name: HelloWorldCaller_cfgplan_prd.xml
     [copy] Copying 1 file to d:\dev\HelloWorld\HelloWorldCaller

replaceset:
     [echo] Processing replacementset: first
     [echo] dev URL: http://192.168.1.1:7001
     [echo] prd URL: http://192.168.1.2:7001
     [echo] Found a reference: Service1

replaceset:
     [echo] Processing replacementset: second
     [echo] dev URL: http://192.168.2.1:8080
     [echo] prd URL: http://192.168.2.2:8080
     [echo] Found a reference: Service1
   [delete] Deleting: d:\dev\HelloWorld\HelloWorldCaller\plantemplate.xml

BUILD SUCCESSFUL
Total time: 2 seconds


After I ran the Ant script I get two configuration plans. One for the development environment and one for the production environment. Below is the one for the production environment. In bold indicated which portions are added/changed by the script.

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<SOAConfigPlan xmlns="http://schemas.oracle.com/soa/configplan" xmlns:edl="http://schemas.oracle.com/events/edl" xmlns:jca="http://platform.integration.oracle/blocks/adapter/fw/metadata" xmlns:orawsp="http://schemas.oracle.com/ws/2006/01/policy" xmlns:wsp="http://schemas.xmlsoap.org/ws/2004/09/policy">
   <composite name="HelloWorldCaller">
      <import>
         <searchReplace>
<search>http://192.168.1.1:7001</search>
<replace>http://192.168.1.2:7001</replace>
</searchReplace>
<searchReplace>
<search>http://192.168.2.1:8080</search>
<replace>http://192.168.2.2:8080</replace>
</searchReplace>

      </import>
      <service name="helloworldcaller_client_ep">
         <binding type="ws">
            <attribute name="port">
               <replace>http://xmlns.oracle.com/HelloWorld/HelloWorldCaller/HelloWorldCaller#wsdl.endpoint(helloworldcaller_client_ep/HelloWorldCaller_pt)</replace>
            </attribute>
         </binding>
      </service>
      <component name="HelloWorldCaller">
         <property name="bpel.config.transaction">
            <replace>required</replace>
         </property>
      </component>
      <reference name="Service1">
         <binding type="ws">
            <attribute name="port">
               <replace>http://xmlns.oracle.com/HelloWorld/HelloWorld/HelloWorld#wsdl.endpoint(helloworld_client_ep/HelloWorld_pt)</replace>
            </attribute>
            <attribute name="location">
               <replace>http://192.168.1.2:7001/soa-infra/services/default/HelloWorld/helloworld_client_ep?WSDL</replace>
            </attribute>
            <property name="weblogic.wsee.wsat.transaction.flowOption">
               <replace>WSDLDriven</replace>
            </property>
         </binding>
      </reference>
   </composite>
   <wsdlAndSchema name="HelloWorld.wsdl|HelloWorldCaller.wsdl|xsd/HelloWorld.xsd|xsd/HelloWorldCaller.xsd">
      <searchReplace>
<search>http://192.168.1.1:7001</search>
<replace>http://192.168.1.2:7001</replace>
</searchReplace>
<searchReplace>
<search>http://192.168.2.1:8080</search>
<replace>http://192.168.2.2:8080</replace>
</searchReplace>

   </wsdlAndSchema>
</SOAConfigPlan>


When testing the generated configuration plan (JDeveloper, right click the configuration plan, Validate Config Plan), the following is shown. This validates the generated configuration plan and shows it functions as expected.

Modified Composite [ HelloWorldCaller ]
    Import Loations
        No change in old and new value HelloWorldCaller.wsdl
        Old [ http://192.168.1.1:7001/soa-infra/services/default/HelloWorld/HelloWorld.wsdl ]
        New [ http://192.168.1.2:7001/soa-infra/services/default/HelloWorld/HelloWorld.wsdl ]

    Component
      Component  [ HelloWorldCaller ]
        Property [ bpel.config.transaction ]
        No change in old and new value required
    Service
      Service  [ helloworldcaller_client_ep ]
        Service Bindings
          Binding  [ ws ]
    Attribute name=port
        No change in old and new value http://xmlns.oracle.com/HelloWorld/HelloWorldCaller/HelloWorldCaller#wsdl.endpoint(helloworldcaller_client_ep/HelloWorldCaller_pt)
    Reference
      Reference  [ Service1 ]
        Reference Bindings
          Binding  [ ws ]
        Property [ weblogic.wsee.wsat.transaction.flowOption ]
        No change in old and new value WSDLDriven
    Attribute name=port
        No change in old and new value http://xmlns.oracle.com/HelloWorld/HelloWorld/HelloWorld#wsdl.endpoint(helloworld_client_ep/HelloWorld_pt)
    Attribute name=location
        Old [ http://192.168.1.1:7001/soa-infra/services/default/HelloWorld/helloworld_client_ep?WSDL ]
        New [ http://192.168.1.2:7001/soa-infra/services/default/HelloWorld/helloworld_client_ep?WSDL ]

---End Match for composite [ HelloWorldCaller ] in config plan---
Checking for replacement in wsdl and schema files

Friday, March 1, 2013

Using PL/SQL object types to get nested or repeating XML structures efficiently to the database with the DbAdapter

There are different methods for getting nested or repeating XML structures to the database. For example, loops on elements of an XML structure can be implemented in BPEL, calling the DbAdapter for every element. See for example http://javaoraclesoa.blogspot.nl/2012/03/loops-in-bpel-11-and-20.html. The method mentioned requires some work and one can question it's efficiency in terms of performance. For every item to be processed, the DbAdapter is called and a transformation is required. An alternative is using PL/SQL object types. PL/SQL object types can contain repeating and nested structures and can be send to the database in a single DbAdapter call. This reduces the overhead caused by calling the DbAdapter (faster) and the complexity of BPEL code (easier). Because no complex (technical) logic is required in the code calling the DbAdapter, this method also allows for easier implementation in for example Oracle BPM. In this post I'll describe how this method can be implemented using a BPEL code example. There are some considerations though when using PL/SQL object types.

Implementation

Database code

The below code shows how a repeating structure can be implemented using a PL/SQL object type;

CREATE OR REPLACE TYPE ITEM
IS
  OBJECT
  (
    NAME  VARCHAR2(255) ,
    VALUE VARCHAR2(255) );

CREATE OR REPLACE
TYPE         ITEM_ARR AS TABLE OF ITEM

The PL/SQL type ITEM_ARR contains an array of PL/SQL objects; ITEM. The object ITEM contains two fields; NAME and VALUE.

To illustrate the usage of the ITEM_ARR type, a sequence, table, trigger are created and a small TAPI (table API) package. The trigger is optional for this example.

CREATE OR REPLACE TRIGGER ITEMS_TAB_BI 
BEFORE INSERT ON ITEMS_TAB
FOR EACH ROW
BEGIN
  IF (:NEW.ID IS NULL) THEN
    SELECT ITEMS_TAB_SEQ.NEXTVAL INTO :NEW.ID FROM DUAL;
  END IF;
END;

CREATE OR REPLACE
PACKAGE BODY ITEMS_TAB_UTILS AS
  PROCEDURE ADD_ITEMS(P_ITEMS ITEM_ARR) AS
  BEGIN
    FORALL x in P_ITEMS.First..P_ITEMS.Last
     INSERT INTO ITEMS_TAB (ID,NAME,VALUE) VALUES (ITEMS_TAB_SEQ.NEXTVAL,P_ITEMS(x).NAME,P_ITEMS(x).VALUE);
  END ADD_ITEMS;
END ITEMS_TAB_UTILS;

To use an object type from another schema, the connecting schema needs to have execute permission on the object type.

BPEL code

In BPEL the ADD_ITEMS procedure can be called. This procedure will bulk insert all received items in one action. From BPEL, the DbAdapter only has to be called once. This is a good thing considering calling the DbAdapter is relatively expensive. In BPEL only one simple transformation is required and no ForEach or While activities have to be used.

The below image shows a short overview of the process and simple exception 'handling'.


I've used the message type of the itemCollection (see also  http://javaoraclesoa.blogspot.nl/2012/03/loops-in-bpel-11-and-20.html). An itemCollection is as the name might suggest, an array of items. An item contains a name and a value. The below image shows the used transformation from the input message to the message used to call the DbAdapter. As can be seen, the repeating elements are present in the target message.



When calling the service from the webservice test page in the Enterprise Manager, it looks as followed;


The result from this call is shown below;


The code can be downloaded here; https://dl.dropbox.com/u/6693935/blog/ItemUtils.zip

Considerations

PL/SQL object types can be used to get repeated or nested structures to the database in an easy and performing way. There are some things to mind though when using PL/SQL object types.

Datamodel changes

The datamodel is defined in XML schema's in the BPEL process and also in the database definition of the object type. When adding a field or changing the order of fields, the database code and the BPEL code need changing. You won't get errors in every situation when you don't do it right, since object type fields are passed in order and not by name. This is a thing to mind when making changes.

Object types in tables and queues

Object types can be used as a type in a database table or a queue message type. My suggestion is not to use object types this way. The below error message says much.

ORA-02303: cannot drop or replace a type with type or table dependents
02303. 00000 -  "cannot drop or replace a type with type or table dependents"
*Cause:    An attempt was made to drop or replace a type that has dependents.
           There could be a substitutable column of a supertype of the type
           being dropped.
*Action:   Drop all type(s) and table(s) depending on the type, then retry
           the operation using the VALIDATE option, or use the FORCE option.

If the object type changes, you will have to consider migration strategies of existing tables and queues. This can be troublesome. Sometimes tables need to be dropped and recreated because an alter type might not be sufficient to achieve the desired change. In a production environment, if the PL/SQL code has not been written in a modular way, making sure the queue is empty and will remain empty during the installation, can be a bother.

Also when a complex datamodel is implemented using PL/SQL object types, the dependencies between the types can become difficult to manage in case of changes. This is illustrated in my example by the following;  I can't CREATE OR REPLACE the ITEM type because it is used in the ITEM_ARR type.

Conclusion

Object types can be used to increase performance and decrease complexity, however changes are more difficult to implement. My suggestion on this is to only use them in the following cases;

- when using them allows you to avoid complex BPEL code
- when performance is important and you want to minimize DbAdapter calls and transformations
- when changes to the data structure implemented in PL/SQL object types, are rare

Don't use them as a column in a table or queue. You are more flexible by using other methods such as using primitive types and spreading the data structure over several tables or by using XMLType or LOB data types.