Email CSV File As Attachment

I had to create a bpel, which gets data from couple of db tables, and create a csv file and send it as email attachment. I bumped into couple of interesting challenges, luckily google is always there to help :), let me capture some of the lessons learnt.

1. creating a csv file in append mode
2. setting/getting filename in the fileadapter
3. handling multiple records, nillable fields in nxsd
4. interesting xslt, xpath functions
5. sending email with attachment


Creating a csv file using file adapter was quite straightforward, you have to get a sample csv file that you want to crate and give that to the fileadapter wizard to create an nxsd (native xsd). more on it creating csv file here reading csv file here

As the file was created in append mode (how-to here), the file name has to be unique, it had to be set by creating a fileadapter header variable and passing it in invoke. If the file name is created by fileadapter, its name can be obtained as prescribed in technotes 10.1.3.3 by adding an output of header type to the write operation.

I had to write a header to the file, and fileadpater supports multiple record types, however all these record types need a condition Value for each record type to follow, which was not possible to define for data w/o starting with any fixed value, so I had to abandon the idea of using records, I printed the header separately as all my fields were string type. So the nxsd can be tweaked to change datatypes or making fields as optional (nillable=true). more here


While working thru transformations came across very effective xslt functions like translate, which replaces any specific character in the string with any other value. function create-delimited-string creates a delimited string out of a particular node in a repeating XML structure.

Finally sending the file as email attachment, bpel sample sendEMailWithAttchment clearly shows how to do it, however I faced two challenges. One was if the ora:readFile cannot find the file it will throw XSLT error (here ), which is difficult to debug. And some reason my text/html data was overwriting the csv data, for which I changed the order or data setting, setting csv first and then text/html.

update-Nov10

I could not use the translate method to replace newline characters, the sql replace(col,chr(10),null) helped there.

Also if you get javax.xml.xpath.XPathExpressionException: FOTY0001: type error for nothing wrong in XSLT, its because you edited the XSLT even before bpel loaded the parts in the Xform activity, so always wait till the parts load before clicking edit for the XSLT.

transaction=participate

The first thing to note is global transactions will only work among sync processes, so an async bpel/esb cannot participate in a global transaction, so the way to make an async process sync is set delievryPersistPolicy to off.immediate.

Now partnerlinks in sync processes participate in global tx in 2 ways
1. set transaction=participate at the partnerlink (child BPEL or esb call) level
2. set transaction=participate at global level under in bpel.xml (It seems default in 10.1.3.4) - This is for all adapter calls (DBAdapter, AQ, JMS etc.) to participate in global tx

Now for the global tx to work, you have to use datasource with global tx enabled and using an XA driver(may not be true XA).

So when there is error (bindingfault) from dbadapter calls, it will mark the tx for rollback, but it will actually not rollback.

What we have seen is you have to set handleTopLevelFault=false in the configurations/bpel.xml so that rollback works. The other approach is to throw rollback exception in catch-all block.

The following seems to be best-way for global tx handling (10.1.3.4 on weblogic 9.2)
1. XA driver with Global tx enabled
2. in catch-all throwing rollback exception

This is rolling back the db inserts, as well dehydrating the bpel.

As bpel dehydration is happening, it seems it’s happening in a separate thread/tx (need to check if its a supported feature in 10.1.3.4). However we also saw the instance getting into recover(invoke) while using handleTopLevelFault=false.

a good discussion in OTN here and here
some good bpel tx details here and here

update on 23/10
10.1.3.4 does support audit in a seprate thread in async mode as per release notes.

ORABPEL-11825Attempt to use an unsupported database platform.

If you get this error -

WSIF JCA Execute of operation 'T' failed due to: Attempt to use an unsupported database platform.Database platform is not supported: oracle.toplink.platform.database.DatabasePlatform; nested exception is: ORABPEL-11825Attempt to use an unsupported database platform.

Then DBAdpater configuration needs set the PlatformClassName, sample values

• oracle.toplink.platform.database.Oracle9Platform
• oracle.toplink.platform.database.DB2Platform
• oracle.toplink.platform.database.SQLServerPlatform

you can also take this value from the MCF property settings.

Dynamic partnerlink

While crating EAI flows involving many bpels/esbs one common requirement is to be able to call some dynamic logic at run-time. For example say we have a set of requester and provider bpels, which transform the source-format to canonical and canonical to destination-format respectively. However we also have a requirement to do certain enrichment to the data if the source is A and some other enrichment if the source is B. since our requester bpel is common to both the sources we need to extend it in a manner that both the specific enrichment requirements for A and B are taken care while creating the canonical. So one-way to do this is to create source specific or "whatever" (which needs extension) specific bpels and call them from the common requester or provder bpels. And which bpel to call gets decided at run-time. This is a typical requirement for creating dynamic partnerlinks. This is covered in detail in this article

So lets say your master bpel is M and you need to call either A or B based on the source system. In order to create a dynamic link to A or B, roughly below are the steps
1. A and B should be based on same wsdl/xsd
2. In M we need to include this wsdl/xsd with an service endpoint url (this url doesn't matter as at runtime it will be replaced with actual url)
3. Creating a variable of type EndpointReference - and set the following XML fragment
[wsa:EndpointReference xmlns:wsa="http://schemas.xmlsoap.org/ws/2003/03/addressing"] [wsa:Address/][/wsa:EndpointReference]
4. The actual address has to be obtained from somewhere, either from database or from a config file
5. Once the actual address is obtained, set it to the EndpointReference address
6. Assign the EndpointReefrence to the partnerlink directly

Please refer to the article for sceenshots.

JSM Cluster in Weblogic

When we had to deploy queues/topics in Weblogic cluster, there were two challenges, how to make the queues load balance and how to failover between nodes. Both the challenges are taken care by creating distributed queues (uniformly) and deploying to the jms servers in soa cluster. For internal JMS clients like bpel both load-balance and failover are taken care by the server. However for external JMS clients (weblogic) we used provider url as t3://host1name,host2name:port to achieve HA and it works.

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.

inside bpel pm

The most common question in bpel is what is sync vs async. And the answer is mostly sync is request-response in a blocking thread and async is non blocking one-way request with a callback later. From the bpel pm perspective the simple answer is sync is two-way invoke and async is one-way invoke. So if your operation has only [input] its async and if it has [input], [output] both then its sync.

Now how does it make a difference performance wise? async requests go through what is called invoke_message table in bpel pm. So when an async request is made this is basically a two-step process -

Step1 - the message is persisted to dehydaration store / invoke_message table and also sent to a JMS queue
Step2- a worker MDB picks up the message from JMS queue and invokes the bpel

Both these steps happen in two separate threads/transactions.

The configuration property which make all these happen is DeliveryPersistPolicy=on


So whenever there is a one-way invoke, its important to recognize that from the client perspective only step1 happens. Step2 happens internally and performance depends on resources (threads, MDBs) availability.

One more property to keep in mind is CompletionPersistPolicy, this policy basically says how much to write while dehydration.

Dehydration happens during 7 different points (middle of the bpel)
1. receive (if first node and transaction=participate set)
2. pick (on message, on alarm)
3. wait
4. invoke (idempotent=false)
5. flow/flowN (nonblockingInvoke = true)
6. reply (idempotentreply=false)
7. dspMaxRequestDepth has reached (default 600)

dehydration happens at two levels, metadata and auditdata.

If there is dehydration in between the bpel flow, its called a durable process else transient. Dehydration helps recover processes in case of any node failures.


I will cover some bpel best practices, esb internals, tuning parameters in coming up posts.

XML Schema extension

Recently working on a ARIBA-EBS integration, we decided to use/extend oagis schema. The approach was to create project specific wrapper xsds with the project specfic namespaces. Wherever there was need to extend a type, the [xsd:extension] was used. An example here, here and here


However whether to use the UserArea to put extension type or to define new types in the extension type - was a open question. Also by replacing the UserArea of type 'any' with our own custom type, in my opinion is defeating the purpose, as it would not be possible to accept any dynamic content at runtime.

One approach for dynamic xml content at runtime xsd polymorphic type [ xsi:type ]

What do you think?