Sync DBAdpater call time-out

While working on a real-time interface to sync receipts to Oracle EBS by using pl/sql apis, I got a sense that DBAdapter is probably the epicenter of all EAI/SOA work (not to forget the fileadapter). There is so much to the DBAdapter. We had to do two things. Firstly to create a DBAdpater partnerlink to call a custom pl/sql api to insert data to open interface tables, Secondly call another custom pl/sql api (which in tern calls a standard oracle api) to move the data from open interface tables to base tables.

The partnerlink creation was quite straight forward using the wizard, we had the customary issues of recreating the partnerlinks again and again when the signature of the api was changing. Since the API had complex data types such as record type and table type the wizard created wrapper packages for the bpel. While recreating we had to take care of the name and namespaces, so that it doesn’t conflict with the mappings we had created. One thing to take care was the order in mapping has to be same as mapping in the XSD. We didn’t use DetectOmission or DirectSQL optimizations in these calls as these were api calls and not direct db inserts.

One major challenge we faced was when the second api was taking too long to respond and the bpel instance went into manual Recover(invokes). In the logs we saw timeout errors for more than 120 secs. So We increased the time-out based on this post also here

This improved the issue, but we still were not getting bpel time-out errors the instance was going into recovery. As it turned out timeout properties on partnerlink only works for soap calls, as suggested here. The next option is to set the DBStoredProcedureInteractionSpec property QueryTimeout (set this in your adapter WSDL, jca operation) this option works for store proc calls in 10.1.3.4, it didn't work for me. We also tried to set deliverypersistpolicy to off.immeidate for the partnerlink.

However some optimizations on the Oracle API side like index creation, and process thread increases improved the performance form bpel drastically.