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.

Logic of assigning Conversation Id for polling BPEL process – SOA 11g

        Recently we ran into an issue where we were getting issues with Conversation ID in SOA .Since we are not directly involved in generating the Conversation ID it was something we had not expected.

So one of the learning’s that we got out of this issue was that the payload data was also being used to create the conversation Id’s.

In our case we were having a composite primary key which was a collection of 4 to 5 columns. There was a DB Adapter polling these records. The error message was thrown not from the process but from the SOA engine.

Error Message: 

0000000000004fd1,0] [APP: soa-infra] JCABinding=>  db_poll2 db_poll2Adapter Service db_poll2 was unable to perform delivery of inbound message to the composite default/ConvIdPollerTest!1.0*soa_15be5514-092f-44ce-b44d-95726a0486f1 due to: Exception [TOPLINK-4002] (Oracle TopLink - 11g Release 1 (11.1.1.6.0) (Build 111018)): oracle.toplink.exceptions.DatabaseException[[

Internal Exception: java.sql.BatchUpdateException: ORA-12899: value too large for column "DEV116_SOAINFRA"."COMPOSITE_INSTANCE"."CONVERSATION_ID" (actual: 125, maximum: 100)

Since it was to do with SOAINFRA tables we had a look at the SOAINFRA COMPOSITE_INSTANCE table. The CONVERSATION_IS column was of 100 char.

The issue was coming because the SOA engine used the primary key values from polled records and concatenated them to a unique Id to make the conversation ID.

Since there were records where the combined concatenated value was more than 100 chars those records where failing when SOA Engine tries to make an entry into COMPOSITE_INSTANCE table for

Instance tracking.

There is no straight forward solution as this is a product limitation. Solution was to have a workaround to find a key which is unique but not too lengthy J

Xpath to retrieve Group Attribute values from Oracle B2B message

 Some time back I had written a blog on How to Configure XPath Expression to retrieve more information from the Business Message in B2B Console .

It was on basic xpaths for retrieving the invoice id and other information based on segments.

//*[local-name()=' Element-143']/text()  -  This works for fetching data from the ST segment (Transaction Type– highlighted below in green).

B2B uses the information from edi file and agreement information to populate Group Information – Group Control Number (GS06) , Group Functional Identifier (GS01).

But for fetching the Group Information – Group Control Number (GS06) , Group Functional Identifier (GS01) XPath expression is bit complex and I am listing them below.

So for example to list GroupSenderId value::

//*[local-name()='Property' and 'GroupSenderID'=@*[local-name()='Name']]/text()

So for example to list GroupReceiverID value ::

//*[local-name()='Property' and 'GroupReceiverID'=@*[local-name()='Name']]/text()

 

B2B message snippet

-------------------------------------------

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

<Transaction-810  XDataVersion="1.0"  Standard="X12"  Version="5010"  GUID="{38353832-3932-3330-3134-323936313736}">

<ns1:Internal-Properties>

<ns1:Data-Structure  Name="Interchange">

<ns1:Lookup  Name="InterchangeSenderID">THO123</ns1:Lookup>

<ns1:Property  Name="InterchangeSenderID">THO123</ns1:Property>

<ns1:Lookup  Name="InterchangeReceiverID">GEO456</ns1:Lookup>

<ns1:Property  Name="InterchangeReceiverID">GEO456</ns1:Property>

<ns1:Data-Structure  Name="Group">

<ns1:Lookup  Name="GroupSenderID">THO123</ns1:Lookup>

<ns1:Property  Name="GroupSenderID">THO123</ns1:Property>

<ns1:Lookup  Name="GroupReceiverID">GEO456</ns1:Lookup>

<ns1:Property  Name="GroupReceiverID">GEO456</ns1:Property>

<ns1:Data-Structure  Name="Transaction"/>

</ns1:Data-Structure>

</ns1:Data-Structure>

</ns1:Internal-Properties>

<ns1:Segment-ST>

<ns1:Element-143>810</ns1:Element-143>

<ns1:Element-329>#ControlNumber#</ns1:Element-329>

</ns1:Segment-ST>

<ns1:Segment-BIG>

<ns1:Element-373_1>20120910</ns1:Element-373_1>

</ns1:Segment-BIG>

……………………………….

<ns1:Segment-TDS>

<ns1:Element-610_1>391262</ns1:Element-610_1>

</ns1:Segment-TDS>

……………………

<ns1:Segment-SE>

<ns1:Element-96>#SegmentCount#</ns1:Element-96>

<ns1:Element-329>#ControlNumber#</ns1:Element-329>

</ns1:Segment-SE>

</Transaction-810>

Thanks To Girish for the help.