Disclaimer : This blog space does not necessarily reflect my views/ideas on the technology and beyond doubt, never reflects the views of my employer.

Saturday, November 15, 2008

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

2 comments:

Tiago said...

Nirav,

Thank you so much for this nice post! It was really helpful to me.

See you!

Sqiar said...

Thank you very much for this article, it is so rare to see nowadays written as fervently article. I enjoyed reading it and I learned a lot of things. I will go and continue reading your blog =). Good luck for the future and another one for the quality of it.You can also check out this (http://www.sqiar.com/consultancy/tableau/).