Calculating difference between 2 datetime values in Oracle BPEL

If you are require to use lots of datetime values, then you may also need to manipulate those different datetime values. Manipulating XPath 2.0 functions may be a problem. For this, You may refer to blogs by Ramkumar Menon and Antony Reynolds.

I'm using XPath 2.0 functions to get date and time in BPEL Process. You can get more information about XPath 2.0 here. Below is the example I have created based on Ramkumar's blog to calculate the duration between two datetime values in Oracle BPEL.


First create two schemas,

input.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="input">
<xsd:annotation>
<xsd:documentation>
A sample element
</xsd:documentation>
</xsd:annotation>
<xsd:complexType>
<xsd:sequence>
<xsd:element name="date1" type="xsd:dateTime"/>
<xsd:element name="date2" type="xsd:dateTime"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>

and output.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="output">
<xsd:annotation>
<xsd:documentation>
A sample element
</xsd:documentation>
</xsd:annotation>
<xsd:complexType>
<xsd:sequence>
<xsd:element name="duration" type="xsd:string"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>


As schemas are ready, we will create the BPEL process now.

In BPEL Process, the namespace for XPath 2.0 is declared like

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

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


and the the this xp20 functions can be used as below to extract the datetime values.

• xp20:current-dateTime() - returns a dateTime element that has the current date and time as formatted above. (e.g. 2008-11-15T11:32:28.256). I'm using this function and I am to calculate difference for variable holding this value.
  
• xp20:current-date() - returns a date element that has the current date as formatted above without the time portion.
• xp20:current-time() - returns a time element that has the current time formatted as above without the date portion.

In BPEL Process, create two variables,

<variable name="inputdate" element="ns1:input"/>
<variable name="outputValue" element="ns1:output"/>

Create the transformation activity,

<assign name="Transform_1">
<bpelx:annotation>
<bpelx:pattern>transformation</bpelx:pattern>
</bpelx:annotation>
<copy>
<from expression="ora:processXSLT('Transformation_1.xsl',bpws:getVariableData('inputdate'))"/>
<to variable="outputValue"/>
</copy>
</assign>


Let's look at how the XSL file is to be designed. We are creating the template "timeDifference" and using the same to find the time difference.

In the starting of the XSL file, definition of the source and target XSD.

<?xml version="1.0" encoding="UTF-8" ?>
<?oracle-xsl-mapper
<!-- SPECIFICATION OF MAP SOURCES AND TARGETS, DO NOT MODIFY. -->
<mapSources>
<source type="XSD">
<schema location="input.xsd"/>
<rootElement name="input" namespace="http://www.example.org"/>
</source>
</mapSources>
<mapTargets>
<target type="XSD">
<schema location="output.xsd"/>
<rootElement name="output" namespace="http://www.example.org"/>
</target>
</mapTargets>


XSL stylesheet version to be changed to 2.0 and looks like

<xsl:stylesheet version="2.0"

...
xmlns:xp20="http://www.oracle.com/XSL/Transform/java/oracle.tip.pc.services.functions.Xpath20"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:ns0="http://www.example.org"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
exclude-result-prefixes="xsl ns0 xsd ora ...">

In next phase define the template and use it to calculate the difference as below

<xsl:template match="/"> <!-- Suggests that template is in this file only -->
<ns0:output>
<ns0:duration>
<xsl:call-template name="timeDifference">
<xsl:with-param name="date1" select="/ns0:input/ns0:date1"/>
<xsl:with-param name="date2" select="/ns0:input/ns0:date2"/>
</xsl:call-template>
</ns0:duration>
</ns0:output>
</xsl:template>

<xsl:template name="timeDifference">
<xsl:param name="date1"/>
<xsl:param name="date2"/>
<xsl:value-of select="(xsd:dateTime($date1) - xsd:dateTime($date2))"/>
</xsl:template>
</xsl:stylesheet>

If you inputs are
2008-11-15T11:32:28.256
2008-11-15T11:33:32.256

the output will be
T1M4.0S



Hope this helps. Let me know if you are facing any issues.

Cheers

Nirav

Connecting SQL Server from JDeveloper

Here is a small example on how to create a SQL Server Connection on JDeveloper.

First of all you will need the sqljdbc.jar. You can download it from the Microsoft site. sqljdbc.jar is available across different versions SQL Server 2000, SQL Server 2005, etc. I'm connecting to SQL Server 2K here using JDeveloper 10133.

Once downloaded, we need to add it to the JDeveloper Libraries. I have listed here the steps to create a user library from JDeveloper Help:

• From the main menu, choose Tools Manage Libraries.

• In the Manage Libraries dialog, select the Libraries tab, then select the User node, and click New.

• In the Create Library dialog, enter a library name, select the Source Path node, and click Add Entry .

• In the Select Path Entry dialog, browse to the location of the drivers for the database you are connecting to. Select the driver files, and click Select.

• In the Create Library dialog, click OK, and in the Manage Libraries dialog, click OK .

We can add this library while creation of databse also, but adding first using Manage Libraries is a good practice.

Now in JDeveloper, go to Connection Navigator, Right-Click on Database and select "New Database Connection" from the context menu. Follow the wizard as displayed below:



Select the last option "Thid Party JDBC Driver" from the drop-down.

On the next page, provide valid UserID and Password.



On step 3

For Driver Class, name is com.microsoft.sqlserver.jdbc.SQLServerDriver, and select the library sqljdbc.jar from Manage Libraries as we have added above.



Define the URL as below
jdbc:sqlserver://<db_instance>:<port>;databaseName=<db_name>;user=<userid>;password=<password>


Click Next and Test the connection.

Let me know if you are facing any issues.

Cheers

Nirav

Note : Before deploying BPEL process, we need to deploy sqljdbc.jar file to two different locations on the server: soa_home/bpel/lib and soa_home/j2ee/home/applib. For more info..., refer to http://forums.oracle.com/forums/thread.jspa?threadID=978304&tstart=0