Convert date times based on TimeZone in XSL

Recently I had a requirement where I had to convert time to different timezones based on the clients different locations. One issue we had was with the table column length which was limited to 20 chars.So we had to format the time to remove the offset as it full timestamp usually has a lenght close to 30 chars.Since we were not able to insert the time directly, we had to come up with a workaround.

I am pasting the XSL code snippet  which can be used to modify the timestamp based on the offset which defines the timezone time difference.

<xsl:variable name="TIMEZONE_DIFF"

                        select="xp20:timezone-from-dateTime(startTime)"/>

          <xsl:variable name="DIFF_SIGN"

                        select="substring($TIMEZONE_DIFF,1.0,1.0)"/>

          <xsl:variable name="DIFF_HOUR"

                        select="substring($TIMEZONE_DIFF,2.0,2.0)"/>

          <xsl:variable name="DIFF_MIN"

                        select="substring($TIMEZONE_DIFF,5.0,2.0)"/>

          <xsl:variable name="DURATION"

                        select='concat("PT",$DIFF_HOUR,"H",$DIFF_MIN,"M")'/>

              <xsl:choose>

                  <xsl:when test="$DIFF_SIGN='+'">                

                    <ns0:StartTime>

                      <xsl:value-of select='xp20:format-dateTime(xp20:add-dayTimeDuration-to-dateTime(startTime,$DURATION),"[M01]-[D01]-[Y0001] [H01]:[m01]:[s01]")'/>

                    </ns0: StartTime >

                  </xsl:when>

                  <xsl:otherwise>

                    <ns0: StartTime >

                      <xsl:value-of select='xp20:format-dateTime(xp20:subtract-dayTimeDuration-from-dateTime(startTime,$DURATION),"[M01]-[D01]-[Y0001] [H01]:[m01]:[s01]")'/>

                    </ns0: StartTime >

                  </xsl:otherwise>

                </xsl:choose>

Hope this helps

DBAdapter workaround for inserting non-ISO date formats

In SOA there will be many projects which will migrate from using db triggers/procedures to transfer data between different databases/systems to using fusion middleware.

One of the main issues that has DB inserts is due to the legacy way in which Date would have been inserted .It may not be following any ISO standards.

DB adapters would be conforming to iso 8601 xs:dateTime format

From Console error snapshot

               *

ERROR at line 1:

ORA-01830: date format picture ends before converting entire input string

<faultType>0</faultType>

<bindingFault>

<part  name="detail">

<detail> Exception Description: The object [27-Jun-2012 7:12:25 ], of class [class java.lang.String], could not be converted to [class java.sql.Timestamp]. Internal Exception: BINDING.JCA-11636 Could Not Convert Timestamp Exception. Unable to convert a string value in the xml to a java.sql.Timestamp. Even though databases accept strings representing timestamps in a variety of formats, the adapter only accepts strings representing them in xml ISO dateTime format. The input value must be in iso 8601 xs:dateTime format, i.e. YYYY-MM-DDTHH:MM:SS.sss-07:00 </detail>

Solution

1.    Use a custom SQL for insert instead of using Out of the box Insert query.

Since the format that we want in tables are non-ISO format, use TO_DATE function in the custom query with the format - TO_DATE(#DATE_VALUE,'DD-Mon-YYYY HH24:MI:SS'),.

Eg: INSERT INTO LOAD (shipment_id, transaction_id, action_code, create_timestamp, update_timestamp, e1_release_start_date,  e1_release_end_date)    VALUES (#E0Z58SHPID, #E0Z58TRNID, #E0ACTI, TO_DATE(#SSTM,'DD-Mon-YYYY HH24:MI:SS'), TO_DATE(#SSUTM,'DD-Mon-YYYY HH24:MI:SS'),)

2.    Check your XSD element types. Change datetime to string.

      <xs:element name="SSUTM" type="xs:datetime" nillable="true"/>

         <xs:element name="SSUID" type="xs:datetime" nillable="true"/>

to

      <xs:element name="SSUTM" type="xs:string" nillable="true"/>

         <xs:element name="SSUID" type="xs:string" nillable="true"/>

That’s it, now you should be able to insert the records propeprly into legacy databases in date formats not supported by the DB adapter.

Transaction rollback in SOA 11g - Error not getting propogated through Toplink

I encountered one error scenario in PS5. Had developed a process in PS5. The process shows all interaction as successful but finally the transaction was getting rolled back. The em console shows the process instance as errored out but no errors were shown.

The process involves multiple DB interactions was triggered by polling a table.

BPEL process instance "1040126" completed

The transaction was rolled back. The work performed for bpel instance "1040126" was rolled back, but the audit trail has been saved for this instance.If this is a sync request, please resubmit the request from the client. If it is an async request, please recover from the recovery console by resubmitting the invoke message.

As always the only option was to go behind the scenes ,enable  logging to TRACE-FINEST for oracle.soa.adapter and monitor the logs.

The issue was due to a CONSTRAINT in the table which was being violated by the polling process which was inserting a value not supported by constraint for that column.

Error from Log File

012-11-08T02:43:31.005-06:00] [fmwDirect-directi02] [ERROR] [] [oracle.soa.adapter] [tid: Workmanager: , Version: 0, Scheduled=false, Started=false, Wait time: 0 ms\n] [userId: <anonymous>] [ecid: 8a5d5fb1e9381abe:13601ac5:13adc7cdc79:-8000-000000000001747c,0] [APP: soa-infra] Database Adapter TestProcess <oracle.tip.adapter.db.InboundWork handleException> Encountered a fatal exception while polling.  Will continue polling but with minimal logging.  Please investigate the fault and manually stop polling from the console if in development and this appears to be a modeling mistake.  BINDING.JCA-11624[[

DBActivationSpec Polling Exception.

Query name: [triggerTransactionSelect], Descriptor name: [triggerTransaction.Transaction]. Polling the database for events failed on this iteration.

Caused by java.sql.BatchUpdateException: ORA-02290: check constraint (STATUS) violated

.

  This exception is considered not retriable, likely due to a modelling mistake.  To classify it as retriable instead add property nonRetriableErrorCodes with value "-2290" to your deployment descriptor (i.e. weblogic-ra.xml).  This polling process will shut down, unless the fault is related to processing a particular row, in which case polling will continue but the row will be rejected (faulted).

               at oracle.tip.adapter.db.exceptions.DBResourceException.createNonRetriableException(DBResourceException.java:690)

Inference

      The DB adapter will throw an error while interacting with the DB layer. There will be some constraint errors which may not be propagated through the Toplink layer, there by not showing up in the console. But logs will not hide them,and will help you to debug the issue.