Inserting repeating XML structures efficiently to the database with the DbAdapter

There are different methods for getting nested or repeating XML structures to the database. For example, loops on elements of an XML structure can be implemented in BPEL, calling the DbAdapter for every element. See for example http://javaoraclesoa.blogspot.nl/2012/03/loops-in-bpel-11-and-20.html. The method mentioned requires some work and one can question it's efficiency in terms of performance. For every item to be processed, the DbAdapter is called and a transformation is required. An alternative is using PL/SQL object types. PL/SQL object types can contain repeating and nested structures and can be send to the database in a single DbAdapter call. This reduces the overhead caused by calling the DbAdapter (faster) and the complexity of BPEL code (easier). Because no complex (technical) logic is required in the code calling the DbAdapter, this method also allows for easier implementation in for example Oracle BPM. In this post I'll describe how this method can be implemented using a BPEL code example. There are some considerations though when using PL/SQL object types




BPEL code

In BPEL the ADD_ITEMS procedure can be called. This procedure will bulk insert all received items in one action. From BPEL, the DbAdapter only has to be called once. This is a good thing considering calling the DbAdapter is relatively expensive. In BPEL only one simple transformation is required and no ForEach or While activities have to be used.

The below image shows a short overview of the process and simple exception 'handling'.

I've used the message type of the itemCollection (see also  http://javaoraclesoa.blogspot.nl/2012/03/loops-in-bpel-11-and-20.html). An itemCollection is as the name might suggest, an array of items. An item contains a name and a value. The below image shows the used transformation from the input message to the message used to call the DbAdapter. As can be seen, the repeating elements are present in the target message.

When calling the service from the webservice test page in the Enterprise Manager, it looks as followed;

The result from this call is shown below;

TIP TO INCREASE DBADAPTER PERFORMANCE


In a few of our services in bpel and osb we’re inserting a lot of data (big payload messages) into the database by use of the jca db adapter.
We noticed performance would drop hugely when the db adapter needed to insert big payloads (CLOBS) and when the stream of messages on these services would continue even more performance would dramastically go down on the system.
After disabling “wrap data types” on the datasource in Weblogic we had a pretty good performance win on processing big clob messages into the database.
To disable wrapping of data type objects do the next :

• Login the Weblogic Console
• Click yourdomain > Services > Data Sources > your_datasource > Connection Pool > Advanced > disable Wrap Data Types

                                              


                                                                                                Happy Learning
                                                                                                DeepthiReddy :)