SOA , Oracle Fusion Middleware , Weblogic, Java and Cloud..: 2012

Saturday, August 4, 2012

Connection to SQl Server from JDeveloper to work with DB Adapters in SOA 11g

This post explains

a) Setup the SqlServer's connection in Jdeveloper 11g.
b) Creating the DB Adapter in Weblogic console to access the SQl Server database.

Steps:

1. Add Oracle’s SQL JDBC driver (Present in Weblogic home/Server/lib) to JDeveloper. Files to copy would be weblogic.jar, wlclient.jar and wlsqlserver.jar. Create a folder called OracleJDBC_MSSQL_Driver under <your path>Oracle/Middleware folder and copy these files in there. Also add these files to the JDeveloper classpath.

2. Create the DB connection with the following :ConnectionType : Generic JDBC.DriverClass : weblogic.jdbc.sqlserver.SQLServerDriver.Library: OracleJDBC_MSSQL_Driver ( browse the above path in which you have the 3 jars)

3. Jdbc url woud be like : jdbc:weblogic:sqlserver://<hostname>:<portname>;DatabaseName=<yourDBName>Note : its a semi colon after port and not a :

4.Create a new JDBC data source with JNDI value you like and database type as MS SQL Server. Selected Database driver as Oracle’s MS SQL Server Driver (Type 4) Version 7.0.

5. Make sure you have the DB Adapter's Conection pool properties like below :

a.datasourcename : Your Sql Server datasource name created in above step.
b.platformClassName - oracle.toplink.platform.database.SQLServerPlatform.
(The default one i.e org.eclipse.persistence.platform.database.Oracle10Platform is for connecting to Oracle Databases.)
c. defaultNchar should be 'false'.
d. sequencePreallocationSize to 50.
e. batchwriting - true.
f. nativesequencing - true.

g. skipLocking - true.

Tuesday, July 31, 2012

Dealing with Multiple Sources in XSLT in SOA 11g

Quite often we need to derive the values into a variable(target) from more than single source.
SOA 11g Transform activity provides us a way to tag multiple sources in the wizard itself as shown below.

Where source1Var and source2Var are two different sources. If you observe closely in the source code for the same transform activity , the code snapshot pasted below

<copy>
<from>ora:doXSLTransformForDoc("xsl/XformSource1Source2ToTarget.xsl", $source1Var, "source2Var", $source2Var)</from>
<to variable="targetVar"/>
</copy>

The first source(source1Var) is passed as the normal source to XSL where as the second one is sent as a parameter to the same with the name source2Var and the value of the same is $source2Var.

Also if you open the xsl in source mode you can find that

....

<xsl:param name="source2Var"/>

....

So now you can use this second source ( anything other than primary source) as a parameter inside the xslt mapping.

Now lets see how we can work on multiple sources in XSLT. Lets say you have requirement like this.

1. Some of Target variables data elements depend on source1var.

2. Some of them depend on source2Var.

3. Some of them are dependent on source1Var and source2Var in a complex way. ie.,Lets assume a scenario such as -

Loop through the source2 for each record of source1 , then find that record whose "id"(of source2) matches with "id" of source1.Now then multiply source1Var/x element with the fetched record of source2Var/y.

1,2 are pretty straightforward with the mapping done directly.

Coming to scenario 3 we may need to tweak xslt a little bit like below by using some local variables.

<xsl:for-each select="source1Var/ yourComplexElementForSource1">

<xsl:variable name="id" select="id"/>

<xsl:variable name="x" select="x"/>

<xsl:variable name="y" select="$source2Var/yourComplexElementForSource2[id= $id)]/y"/>

<xsl:value-of select="$x * $y"/>

</targetVarElement>

</xsl:for-each>

If you observe we are using an inline xpath matching of the value id of source2(id) with id of source1($id). This acts like another loop(on source2's complex element) for us ( avoiding a real loop to be written).

So thats it ! It works.

A related question is posted on oracle forums recently- https://forums.oracle.com/forums/thread.jspa?threadID=2421631&tstart=0

Please find below is the solution for the same using the above mentioned approach.

A Sample implementation of the concept exaplained above:

source1.xsd

<?xml version="1.0" encoding="windows-1252" ?>

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"

xmlns="http://www.example.org"

targetNamespace="http://www.example.org"

elementFormDefault="qualified">

<xsd:element name="docTypeRef_tns_RetrieveGetDataResponse">

<xsd:complexType>

<xsd:sequence>

<xsd:element name="instrumentDatas">

<xsd:complexType>

<xsd:sequence>

<xsd:element name="instrumentData" maxOccurs="unbounded">

<xsd:complexType>

<xsd:sequence>

<xsd:element name="instrument">

<xsd:complexType>

<xsd:sequence>

<xsd:element name="id" type="xsd:string"/>

<xsd:element name="yellowkey" type="xsd:string"/>

</xsd:sequence>

</xsd:complexType>

</xsd:element>

<xsd:element name="data" maxOccurs="unbounded">

<xsd:complexType>

<xsd:attribute name="value" type="xsd:string"/>

</xsd:complexType>

</xsd:element>

</xsd:sequence>

</xsd:complexType>

</xsd:element>

</xsd:sequence>

</xsd:complexType>

</xsd:element>

</xsd:sequence>

</xsd:complexType>

</xsd:element>

</xsd:schema>

source2.xsd

<?xml version="1.0" encoding="windows-1252" ?>

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"

xmlns="http://www.example.org"

targetNamespace="http://www.example.org"

elementFormDefault="qualified">

<xsd:element name="MyElement">

<xsd:complexType>

<xsd:sequence>

<xsd:element name="TmsBmbrgRateslist" maxOccurs="unbounded">

<xsd:complexType>

<xsd:sequence>

<xsd:element name="curcyPair" type="xsd:string"/>

<xsd:element name="rate" type="xsd:integer"/>

<xsd:element name="rDate" type="xsd:integer"/>

<xsd:element name="attrib1" type="xsd:string"/>

<xsd:element name="attrib2" type="xsd:string"/>

<xsd:element name="attrib3" type="xsd:integer"/>

<xsd:element name="attrib4" type="xsd:string"/>

</xsd:sequence>

</xsd:complexType>

</xsd:element>

</xsd:sequence>

<xsd:attribute name="xsi" type="xsd:string"/>

<xsd:attribute name="schemaLocation" type="xsd:string"/>

</xsd:complexType>

</xsd:element>

</xsd:schema>

target.xsd

<?xml version="1.0" encoding="windows-1252" ?>

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"

xmlns="http://www.example.org"

targetNamespace="http://www.example.org"

elementFormDefault="qualified">

<xsd:element name="F1113Collection">

<xsd:complexType>

<xsd:sequence>

<xsd:element name="F1113" maxOccurs="unbounded">

<xsd:complexType>

<xsd:sequence>

<xsd:element name="c1Rtty" type="xsd:string"/>

<xsd:element name="c1Crdc" type="xsd:string"/>

<xsd:element name="c1Crcd" type="xsd:string"/>

<xsd:element name="c1Crr" type="xsd:string"/>

</xsd:sequence>

</xsd:complexType>

</xsd:element>

</xsd:sequence>

<xsd:attribute name="ns2" type="xsd:string"/>

<xsd:attribute name="xsi" type="xsd:string"/>

<xsd:attribute name="schemaLocation" type="xsd:string"/>

</xsd:complexType>

</xsd:element>

</xsd:schema>

Requirement :

Populate "c1Crr" field of target based on below logic.

if(<instrumentData>/<instrument>/<id> = <TmsBmbrgRateslist>/<curcyPair>)
{
c1Crr = <instrumentData>/data[2]/@value * <TmsBmbrgRateslist>/attrib3
}

XSLT Mapping:

<?xml version="1.0" encoding="UTF-8" ?>

<?oracle-xsl-mapper

</source>

</source>

</mapSources>

</target>

</mapTargets>

<xsl:stylesheet version="1.0"

xmlns:bpws="http://schemas.xmlsoap.org/ws/2003/03/business-process/"

xmlns:xp20="http://www.oracle.com/XSL/Transform/java/oracle.tip.pc.services.functions.Xpath20"

xmlns:mhdr="http://www.oracle.com/XSL/Transform/java/oracle.tip.mediator.service.common.functions.MediatorExtnFunction"

xmlns:bpel="http://docs.oasis-open.org/wsbpel/2.0/process/executable"

xmlns:oraext="http://www.oracle.com/XSL/Transform/java/oracle.tip.pc.services.functions.ExtFunc"

xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

xmlns:ns0="http://www.example.org"

xmlns:dvm="http://www.oracle.com/XSL/Transform/java/oracle.tip.dvm.LookupValue"

xmlns:hwf="http://xmlns.oracle.com/bpel/workflow/xpath"

xmlns:xsl="http://www.w3.org/1999/XSL/Transform"

xmlns:med="http://schemas.oracle.com/mediator/xpath"

xmlns:ids="http://xmlns.oracle.com/bpel/services/IdentityService/xpath"

xmlns:bpm="http://xmlns.oracle.com/bpmn20/extensions"

xmlns:xdk="http://schemas.oracle.com/bpel/extension/xpath/function/xdk"

xmlns:xref="http://www.oracle.com/XSL/Transform/java/oracle.tip.xref.xpath.XRefXPathFunctions"

xmlns:xsd="http://www.w3.org/2001/XMLSchema"

xmlns:bpmn="http://schemas.oracle.com/bpm/xpath"

xmlns:ora="http://schemas.oracle.com/xpath/extension"

xmlns:socket="http://www.oracle.com/XSL/Transform/java/oracle.tip.adapter.socket.ProtocolTranslator"

xmlns:ldap="http://schemas.oracle.com/xpath/extension/ldap"

exclude-result-prefixes="xsi xsl ns0 xsd bpws xp20 mhdr bpel oraext dvm hwf med ids bpm xdk xref bpmn ora socket ldap">

<xsl:param name="source2Var"/>

<xsl:template match="/">

<ns0:F1113Collection>

<xsl:for-each select="/ns0:docTypeRef_tns_RetrieveGetDataResponse/ns0:instrumentDatas/ns0:instrumentData">

<ns0:F1113>

<ns0:c1Rtty>

<xsl:value-of select='string("A")'/>

</ns0:c1Rtty>

<ns0:c1Crdc>

<xsl:value-of select="substring(ns0:instrument/ns0:id,4,3)"/>

</ns0:c1Crdc>

<ns0:c1Crcd>

<xsl:value-of select="substring(ns0:instrument/ns0:id,1,3)"/>

</ns0:c1Crcd>

<xsl:variable name="id" select="ns0:instrument/ns0:id"/>

<xsl:variable name="dataValue" select="ns0:data[2]/@value"/>

<xsl:variable name="attrib3"

select="$source2Var/ns0:MyElement/ns0:TmsBmbrgRateslist[(ns0:curcyPair = $id)]/ns0:attrib3"/>

<ns0:c1Crr>

<xsl:value-of select="$dataValue * $attrib3"/>

</ns0:c1Crr>

</ns0:F1113>

</xsl:for-each>

</ns0:F1113Collection>

</xsl:template>

</xsl:stylesheet>

References:
1. http://blogs.oracle.com/soa_how_to/entry/how_to_implement_multi-source_xslt_mapping_in_11g_bpel
2. http://java.net/downloads/oraclesoasuite11g/Transformations/mapper-105-multiple-sources.zip

Wednesday, June 27, 2012

Element Not Null check in XSLT:

This basically includes two checks :
1. Element not present / existing.
2. Element existing and not empty.

Solution 1:

<xsl:if test="not(ns1:VisitElement/ns1:visitSequence) or
string-length(ns1:VisitElement/ns1:visitSequence)=0">
<xsl:value-of select="string('HELLO')"/>
</xsl:if>

Explanation : In the above example visitSequence is checked for no existence (or) if it exists if its length is zero. In this case we are replacing the value with string called " HELLO"

Another way for doing the same is as below :

Solution 2:

<xsl:if test="not(ns1:VisitElement/ns1:visitSequence) or
ns1:VisitElement/ns1:visitSequence[.!='']">
<xsl:value-of select="string('HELLO')"/>
</xsl:if>

The expression [.!=''] means that the current node (represented by dot) is not equal(!=) to an empty string('').

Tuesday, May 1, 2012

Scheduling a Process in SOA 11g

"Scheduling" is one of the most common tasks that many projects require. We may need to initiate a process on a specific time based on a schedule i.e, in a particular day of a month or in an hour/min in a day .This feature is not available out-of-box in SOA 11g. Most common and easy to use approach is to use the Quartz scheduler and its supported java classes that can be used to schedule the process based on a time schedule.

Outline of the Steps to Schedule a BPEL Process using Quartz are as follows:

0. Create and make the wsdl of the BPEL process that needs to be scheduled available.

1. Create a WebService Proxy for the BPEL process that you may want to schedule.
This will create a Client and a Port java classes which can be used to invoke the bpel process operations from java code.

2. Create a Job( class that implements org.quartz.Job) that calls the BPELProcess operation(s)( using its client and port classes that were created as a result of step 1).

3. Create a JobTrigger class trigger that will trigger the above Job in a specific schedule.

In Detail here is how it works :

Assuming the Process to be scheduled is HelloWorld Process and its wsdl is - http://localhost:8001/soa-infra/services/default/HelloWorldProject/HelloProcess.wsdl

Detailed Steps :

a. Create a Generic Application and name it SoaScheduleApp.

b. Create a Project(say SoaSchedulerProject) and select java,webservices as project technologies.

c. Create a new Web Service Proxy (webservices),selecting JAX-WS client style for the HelloWorld wsdl -
http://localhost:8001/soa-infra/services/default/HelloWorldProject/HelloProcess.wsdl. You may prefer to copy the wsdl into the project. Select some package name and root package for genric types. Click finish.

d. This will create the HelloProcess_client_ep.java which is a client Service that contains methods returning Port of the Service.( ex: getHelloProcess_pt()). The Port is of type HelloProcess interface which represents a Port that defines operations of the Service.

e. Add Quartz Library to the project.
Click on “Application”-> “Project Properties”,-> " Libraries and Classpath"->“Add JAR/Directory”.
Select in your JDeveloper home “…\jdeveloper\soa\modules\quartz-all-1.6.5.jar”. Click “Select”.
Click Ok.

f. Create a Job component - a Java class which contains the following code.

package sample.oracle.otn.soascheduler.job;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import sample.oracle.otn.soascheduler.proxy.Helloprocess_client_ep;
import sample.oracle.otn.soascheduler.proxy.HelloProcess;
import javax.xml.ws.WebServiceRef;
import org.quartz.Job;
import org.quartz.JobExecutionContext;

import sample.oracle.otn.soascheduler.proxy.Helloprocess_client_ep;

public class DemoJob implements Job{
@WebServiceRef
private static Helloprocess_client_ep helloworldprocess_client;
public DemoJob() {
helloworldprocess_client = new Helloprocess_client_ep();
}
public void execute(JobExecutionContext jobExecutionContext) {
DateFormat df = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
Date date = new Date();
System.out.println("HelloWorldJob started");
try {
helloworldprocess_client = new Helloprocess_client_ep();
HelloProcess helloWorldProcess = helloworldprocess_client.getHelloProcess_pt();
// Add your code to call the desired methods.
System.out.println("HelloWorld Response: " + helloWorldProcess.process("SOAScheduler@" +
df.format(date)));
} catch (Exception e) {
System.out.println("HelloWorld Process failed: " + e.toString());
e.printStackTrace();
}
}
}

Observe that it Implements org.quartz.Job and contains the code to call the BPEL process operation in its method execute(..)

g. Create a Job Scheduler class that triggers the above Job using Cron expressions.
This JobScheduler does 3 things .
i. Creates multiple JobDetails using org.quartz.JobDetail.
ii. Configure Scheduler time using org.quartz.CronTrigger.setCronExpression(..)
iii. Create the schedule using org.quartz.Scheduler.

Code looks like this :

package sample.oracle.otn.soascheduler.job;

import java.util.Map;

import org.quartz.CronTrigger;
import org.quartz.JobDetail;
import org.quartz.Scheduler;
import org.quartz.impl.StdSchedulerFactory;

public class JobTrigger {
public static void main( String[] args ) throws Exception
{
//scheduler task details
JobDetail job = new JobDetail();
job.setName("someJob");
job.setJobClass(DemoJob.class);
//
JobDetail job2 = new JobDetail();
job2.setName("someOtherJob");
job2.setJobClass(DemoJob.class);

//configure scheduler time
CronTrigger trigger = new CronTrigger();
trigger.setName("someJobTrigger");
trigger.setCronExpression("0/30 * * * * ?");

//configure scheduler time 2
CronTrigger trigger2 = new CronTrigger();
trigger2.setName("someOtherTrigger");
trigger2.setCronExpression("0/20 * * * * ? 2013");

//create the schedule
Scheduler scheduler = new StdSchedulerFactory().getScheduler();
scheduler.start();
scheduler.scheduleJob(job, trigger);
scheduler.scheduleJob(job2, trigger2);
}

}

h. Thats it !!. Now its time for execution. Run the JobTrigger class's main method and observe that your BPEL process is being triggered for every 30 seconds.

For understanding the cron expression refer - http://www.quartz-scheduler.org/documentation/quartz-1.x/tutorials/crontrigger

Re-usability:

Now that we are aware of the steps to schedule a bpel service, lets talk about how we can extend and reuse this across projects.
Lets say, in some later point of time, you may need to schedule another process with a different time schedule. example : A Pharma company wants to check its inventory availability every 10 days and make some decisions to reach or not reach to suppliers. For doing so rather than re-inventing the wheel all again we shall do the following.

1. Create WebService Proxy for new Process WSDL.
2. Create another Job component to execute the new process Operation.
3. Reuse the JobTrigger class by making the required additions to create a new JobDetail and schedule the JobDetail using the new cron expression.