Oracle SOA: Sending delayed JMS messages

Sometimes you might want to put something on a JMS queue and make it available after a certain period has passed to consumers. How can you achieve this using Oracle SOA Suite?

SOA Suite 12.2.1: A first look at end-to-end JSON support in SOA Composites

SOA Suite 12.2.1 introduces end-to-end JSON support in composites, support for JavaScript in BPEL and a JavaScript embedding activity. The REST-binding (which can be used by Service Bus, BPEL, BPM) can receive and send untyped JSON without the need to translate it to XML. In BPEL, JavaScript can be used as expression language in various activities and there is a JavaScript embedding activity available.

In this article I'll show some examples on what you can do with this end-to-end JSON support and give some examples on how to use JavaScript in your BPEL process.

Oracle SOA Suite 11g and 12c: Determining composite dependencies to the level of individual operations

In large companies, often there are many services and dependencies between services. It is important to track service dependencies in order to for example estimate the impact of changes. Design documents or architecture views can be used for this but as everybody knows, there is often a gap between theory and practice (design and implementation).

In this blog post I provide code to determine dependencies between composites to the level of operation calls. In order to achieve this, I'll parse the composite.xml files, JCA files (used by adapters) and also the BPEL and BPMN files in order to determine the operations. The script can be used for SOA Suite 11g and 12c composites.

The above picture shows different parts of which a composite is composed and how they are linked. The script first determines references. The references specify which external services are called. Then by using wires, the relevant components are determined. Based on the component type, specific logic is used to extract the operation. Not shown in this picture is how database dependencies can also be determined by the script by parsing the JCA files specified in the reference. If you're in a hurry, you can go to the 'Executing the script' part directly and skip the explanation.

Oracle SOA Suite 12c: The LDAPAdapter, a quick and easy tutorial

In enterprises, LDAP servers are often used to store user credentials and groups and share them among applications. Oracle SOA Suite 12c introduces a new technology adapter; the LDAPAdapter which allows easy integration with LDAP servers. In this blog post I will provide a quick and easy howto on installation of an LDAP server and browser (ApacheDS and Apache Directory Studio). I will also describe the configuration required to use the LDAPAdapter and provide an example BPEL process which allows creation of new users in the ApacheDS LDAP server by means of webservice calls.

Controlling BPEL process flow at runtime

When using Oracle BPEL it is often desirable to allow a certain amount of configuration of the process flow at runtime. To allow configuration, properties/preferences/parameters can be used. These can be implemented in various ways. Lucas Jellema has described a method in the Oracle SOA Suite 11g Handbook (http://www.amazon.com/Oracle-SOA-Suite-Handbook-Press/dp/0071608974 page 252) for system parameters which uses Business Rules. In this blog post I compare three other methods; using Domain Value Maps, using BPEL preferences and using a database table. I look at performance, development, re-use potential, updating the preference at runtime and when to use which method.

Oracle SOA fault handling with ordered guaranteed delivery using BPEL, JMSAdapter, custom JMS headers, JMS message selectors, a JMS topic and durable subscribers

In this blog post I will demonstrate an error handling mechanism which can be used to achieve guaranteed delivery of messages in a specific order. To implement this I have used Oracle SOA Suite 11g, BPEL 2.0. The method described however can also be implemented by other technologies/languages. The method used is different from (as far as I know) previously described methods to implement exception handling in Oracle SOA Suite. A single JMS topic is used and JMS message selectors based on custom JMS headers are used by clients. By using a JMS topic, the services are loosely coupled. This method also allows tracing of flows over the topic in the Enterprise Manager Fusion Middleware Control (when Oracle SOA Suite is used). A screenshot of this mechanism implemented is shown in the screenshot below. It will be explained in more detail below.

JDBC from the Oracle Service Bus

There are different ways to do database calls from the Oracle Service Bus. In this blog post I look at several methods.

The methods looked at;
1 using an external webservice (without OSB)
2 using an external webservice proxied by the OSB

3 using the fn-bea:execute-sql function from the OSB

4 using the JCA DbAdapter from the OSB provided by Oracle as part of Oracle SOA Suite

Oracle SOA Blackbelt training June 2013 Berlin

The Oracle SOA Blackbelt training in Berlin in June of 2013 has provided me with some valuable new insights into various topics related to Oracle SOA Suite. Below are some examples of things which I found interesting to share. These are mostly not literally from the slides but written down in my own words. Some examples have been expanded a bit by additional resources I've found. This not a complete list as the training covers quite a lot of material. I've focused on topics which can relatively easily be implemented or considered. The training also covered a lot of background which is harder to summarize and make concrete in practices/suggestions. The topics are various and not written down in a particular order.

SOA Best practices

The presentation on SOA best practices contained a lot of good suggestions. These are a few of them.

Problem; Over usage of dehydration causes much overhead. Examples; synchronous non-idempotent services, multiple mid-process receives, dehydrate/wait activities in processes.
Recommendations; Avoid chattiness, design services to be idempotent, if possible avoid asynchronous services (callbacks cause thread/transaction overhead)

Problem; Usage of FlowN where N is unconstrained can cause resource problems and lack of control.
Recommendation; Do not base N in FlowN on the data. Design the process using the driver / worker pattern (driver hands small chunks to the worker and the worker processes this). This can for example be implemented by using queues for decoupling/performance.

Problem; Asynchronous services cause overhead. This can become a problem if there are large numbers of asynchronous processes waiting for a response since for every callback, a new thread/transaction is needed and a callback needs to be matched to a correlation table which takes longer if there are a lot of open processes.
Recommendation; Design processes to be synchronous as much as possible. avoid nesting of asynchronous processes. also avoid synchronous processes calling asynchronous processes

Problem; A single BPEL process does batch processing of a large amount of messages. This takes a lot of memory and causes a lot overhead for storing audit information.
Recommendations; Put the work to be done in a separate BPEL process and optimize this process.  design for worst case scenario's. implement retry mechanisms in fault-policies. implement your own scheduling mechanism to spread the load. if no message level processing is needed, ODI might be an option.

Problem; Scope variables are dehydrated and when the variables become large, this causes overhead.
Recommendations; Use local variables whenever possible. assign portions of the message to scope variables.

BPEL is meant for service orchestration. It's not a procedural programming language.
Recommendations; use declarative constructs instead of elaborate custom constructions. use the skip condition instead of if statements. use assertions before and after invokes. use pick activities to time responses

Problem; Identifying BPEL processes can be difficult due to lack of business content in the EM views.
Recommendation; Set the composite title to a business value. it is possible to search for this name. business transaction keys and sensors (both have to be custom implemented) can be used to identify a flow instead of only the ECID since the ECID can not always be traced back to business context.

Diagnostics

I usually tend to look in the log files and in the Enterprise Manager if something goes wrong. There are however several other options;
- creating dumps. the following provides a nice overview; http://docs.oracle.com/cd/E25178_01/admin.1111/e10226/soacompapp_diag.htm#BABJFIFG
- collect info from the MBean browser; oracle.as.soainfra.bpm; Server/bpel:CubeDispatcher ReadXMLDispatcherTrace and oracle.as.soainfra.bpel; Server/BPELEngine:SyncProcessStats and AsyncProcessStats

Database growth (BPEL/BPM)

A concern for managing SOA Suite installations is the growth in database size. It is essential to think about a cleaning strategy. What I learned during the training is that with some programming practices the amount of information saved can also be reduced.

What causes growth;
- creating process instances
- updates to a message payload (workflow)
- asynchronous operations
- process scopes, task assignments. looping back to tasks
- audit (entry and exit of scope and model elements)

Thus it can be more efficient to build larger processes instead of multiple smaller ones. Also scoping and the use of a lot of model elements causes the database size to increase. For example, it could be (untested) more efficient (database space wise) to create a single assign activity with a lot of actions in it instead of several smaller assign activities.

Authorization, authentication and policies

Oracle Platform Security Services provides an abstraction layer to authorization/authentication/role/group providers. In Oracle BPM, users/groups are used but also application roles. The users/groups can be stored by LDAP providers. If there are multiple LDAP providers, these providers can be virtualized by Oracle Virtual Directory (OVD). When OVD is not available at a customer, libOVD can be used to provide a limited lightweight alternative. See for example http://fusionsecurity.blogspot.nl/2012/06/libovd-when-and-how.html. Application roles are stored in a policy store which can also be LDAP based. See for example; http://docs.oracle.com/cd/E12839_01/core.1111/e10043/cfgauthr.htm. Another option is to have it database based. See for example; http://redstack.wordpress.com/2011/10/29/soa11g-database-as-a-policy-store/. Identities can be queried via the browser, for example at; http://localhost:7001/integration/services/IdentityService/identity.

Transactions

The Blackbelt training contained a presentation on the BPEL Engine internals. This provided some additional points to pay attention to when developing. There are 4 'types' of BPEL processes. These 4 types can be categorized by 2 properties; synchronous or asynchronous and durable or transient. The different types behave differently in respect to transactions and threads. This has consequences for exception handling/propagation. The following should be avoided; a transient asynchronous process and a durable synchronous process. Transaction semantics can also have consequences for performance. See for example; http://javaoraclesoa.blogspot.nl/2013/06/oracle-soa-11g-bpel-transaction.html

The Event Delivery Network

Events can be published with different settings; guaranteed delivery and once-and-only-once (OAOO). With the guaranteed delivery setting, local transactions are used (non-XA), the EDN_EVENT_QUEUE is used and there is the possibility of duplicate messages (in case of catastrophic failures). Also, the dequeue transaction is committed when all subscribers have received the message. Retries are not possible in case one subscriber fails to pickup the message. Once and only once setting works differently. A second queue is used; EDN_OAOO_QUEUE. Each subscriber picks up the message in it's own transaction. An XA connection is used with global transactions and the dequeue action can be retried. The EDN can be debugged in the following ways; by using the EDN servlet; http://<host_name>:<port_number>/soa-infra/events/edn-db-log (when EDN is AQ based (which is the default)). This servlet uses the EDN_LOG_MESSAGES table in the SOAINFRA schema. The following loggers are related oracle.integration.platform.blocks.event, oracle.integration.platform.blocks.event.saq, oracle.integration.platform.blocks.event.jms. In the Enterprise Manager, the log level can be tuned. The delivery of messages can be paused by setting the 'Paused' property of oracle.as.soainfra.config/EDNConfig:edn in the System MBean browser.

Local Invocation Optimization

If certain criteria are met, the SOAP/HTTP layer can be skipped when calling a service. The criteria are; the processes have to be on the same server, client/server policies must allow it (this is a property in the policy file). The same server requirement has implications for the use of loadbalancers in cluster configurations. Check the following part of the documentation for more details on how the 'same server check' is performed; http://docs.oracle.com/cd/E28271_01/admin.1111/e10226/soainfra_config.htm. You should also check out https://forums.oracle.com/thread/2302988 for some more information on how to make sure local optimization is used in case of clustering/load balancing setup's. A recommendation is to avoid too many small processes since this increases complexity (in order to achieve local optimization) and overhead.

BPEL fault handling best practices

The following best practices were mentioned in the training (I've rephrased them for brevity);
- always have a catch-all block (selectionFailure for example cannot be caught by using a fault policy)
- use named exceptions for business faults
- when using fault policies, always have a default action
- rethrow faults from fault policies in order to catch them in a BPEL process (when no fault handling action has been defined in the policy)
- notify the source system something has gone wrong
- think about how enable automatic recovery can have impact on transactions and business functionality. this can also be disabled; http://www.albinsblog.com/2011/10/oracle-soa-suite-11g-disabling-auto.html#.Ue_sxm3JWVA
- for asynchronous processes, after a mid-process receive, check the response (which can contain business faults) and terminate the process on error after sending a message to a notification service

MDS

An efficient way to use the MDS is to use a local filebased repository during development and use the database based MDS during runtime. MDS configuration is stored in adf-config.xml files. MDS files can be referred to by a path ; <Store_Root>/<Partition>/<Namespace>/<Resource>. When an MDS object changes, all dependant resources need to be recompiled. The MDS can be used to avoid server startup issues due to dependencies. See; https://blogs.oracle.com/aia/entry/aia_11g_best_practices_for_dec.

Mediator

The Mediator is the only product which has an out of the box resequencer to provide ordening of messages. See for example; http://docs.oracle.com/cd/E17904_01/integration.1111/e10224/med_resequencer.htm for details. Also it currently is the only component supporting Schematron based validations (see for example; http://beatechnologies.wordpress.com/2011/04/06/using-schematron-in-oracle-soa-suite-11g-for-validating-xml/). The Mediator has an Hearbeat infrastructure; if in a clustered environment one instance of a Mediator fails (for whatever reason) this is detected and the other node in the cluster will process the message. Information on message 'lease' is stored in the table; MEDIATOR_CONTAINERID_LEASE. A message is locked when processing starts and released afterwards. The Heartbeat framework can be configured. See; http://docs.oracle.com/cd/E14571_01/integration.1111/e10226/med_config.htm#BABEDHBJ. Sequential routing rules are executed in a single transaction and thread. Parallel routing rules use 3 threads; inbound threads, locker threads and worker threads. Each worker thread uses it's own transaction. Parallel routing rules can be debugged by looking at the MEDIATOR_DEFERRED_MESSAGE. One row is one message in a parallel routing rule.

Oracle Service Bus

Local transport can be used for internal service chaining (for example when calling reusable components). Local transport services can not be invoked from outside the service bus and are not published to a UDDI. See for example http://docs.oracle.com/cd/E23943_01/dev.1111/e15866/local.htm. The split/join pattern which can be implemented in the OSB uses a BEA BPEL implementation and works in-memory (not persistent). The service bus is minimalistic; by default, most features are turned off and the focus is on performance/high throughput. Oracle BPEL for example is more maximalistic and if you want high performance there you should turn things off.

Cube engine internals

Work items for the same instance are not allowed to execute concurrently. This implicates that the parallel execution in for example for-each/while loops is 'simulated' and not truly parallel. This knowledge helps understanding the behaviour of the nonBlockingInvoke setting. See; http://docs.oracle.com/cd/E23943_01/core.1111/e10108/bpel.htm#ASPER99890. The nonBlockingInvoke setting creates new threads for invocations but the invocations are still executed in sequence. In practice, this leads to performance degradation.

Oracle SOA 11g BPEL transaction semantics and performance

In this post I'll provide a simple integration example and provide some suggestions to optimize the performance. Optimization suggestions are focused on transaction semantics. It's purpose is to indicate the importance to take into account various settings related to transaction management.

The base and inspiration for this post are the presentations and material from SOA Blackbelt training which was given by Oracle in Berlin this year from the 11th to the 14th of June. The training covered a lot of material in great depth. If you have the chance to follow it, I highly recommend it!

Test setup

First I enqueue 2000 messages on an Oracle AQ and take a timestamp which I write in a separate table in the same transaction. After a COMMIT, a BPEL process is triggered and picks up the messages (one instance per message). This process puts the message in a table. The moment of insertion is determined by having a default value on a field in the table. I then determine the time difference between the last message put in the table in the batch and the moment of insertion in the AQ. To avoid the overhead of audit logging, I turn this off for the specific process. The code used can be downloaded at the end of this post.

I will vary the bpel.config.oneWayDeliveryPolicy. I will try sync and async.persist (the default). async.persist will first put dequeued messages in the DLV_MESSAGE table before they are further processed in a separate transaction. sync will not do this and will invoke the BPEL process synchronously. I use three different datasource settings for this test. I will try both oneWayDeliveryPolicy settings with an XA datasource (which uses a 2-phase commit) and two non XA datasources. For the non XA datasource I will test with and without Global Transaction support. I will test this using different datasources and the same datasource. I will also test all combinations with the bpel.config.transaction setting to required and requiresNew.

Summarized; four different settings are varied in all combinations of the other settings. Two measures are taken with each combination.
- bpel.config.oneWayDeliveryPolicy (async.persist, sync)
- bpel.config.transaction (required, requiresNew)
- different datasource settings; XA, NonXA, NonXA no global transaction support
- using the same and different datasources with the same settings

I created 6 datasources with the three different settings;
testuserXa, testuserXa2
testuserNonXa, testuserNonXa2
testuserNonXaGlobal, testuserNonXaGlobal2

Next I created 6 connection factories for the DbAdapter and 6 connection factories for the AqAdapter. I varied the datasources in the JCA files in the BPEL process I created. For every test I primed the datasources/engine with 100 messages. Next I took 2 measures of 2000 messages.

Results

The * indicates the process failed with the following exception;
BINDING.JCA-11616
DBWriteInteractionSpec Execute Failed Exception.
insert failed. Descriptor name: [WriteToStore.TestStore].
Caused by java.sql.SQLException: Cannot call Connection.commit in distributed transaction.  Transaction Manager will commit the resource manager when the distributed transaction is committed..

As can be seen, using an XA datasource decreased performance. Also using the sync property increased performance. In this example, little effect was visible when changing the transaction property. There was little difference in using two different datasources instead of a single datasource for processing. The NonXa datasource with global transaction support executed an explicit commit (apparently) which conflicted with the distributed nature of the transaction. This happened in all cases when performing an insert action using this datasource. This also indicates the transaction was distributed in all cases (even when using a Non Xa datasource without global transaction support).

Conclusion

When using the oneWayDeliveryPolicy setting of sync, the entire process is processed in a single transaction. When using async.persist, 2 transactions are involved. One to write to the DLV_MESSAGE table and one to call the DB insert.

The performance impact of writing to DLV_MESSAGE and the extra transaction was measurable. When calling a process synchronously, the effect would have been greater since then it would have been 4 transactions when using the async.persist setting.

Using an XA datasource means a two-phase commit is used. This has a slight overhead which is measurable in this example. It also provides a difference in behavior, mainly in the event of a fault. See for example; http://soaranch.wordpress.com/2010/10/08/global-and-local-transactions-in-oracle-soa-11g-composites/.

Because of the process setup, I could not measure much effect on the transaction setting since the process initialization by the Aq adapter always starts a new transaction. When a process is called from another process, I would have expected to see a performance gain with the 'required' setting since then I woukld have expected less transactions.

I would have liked to see if the DbAdapter and AqAdapter behaved differently when different datasources on the same database schema were used to connect instead of the same datasource. The only difference found however was that when using async.persist, requiresNew and using the same NonXA datasource without global transaction support, errors occurred. These errors did not occur when using different datasources (also NonXA without global transaction support). Apparently with these settings, an explicit commit is executed when performing an insert by using the DbAdapter. Also the incoming message was read using the AqAdapter and the result was written using the DbAdapter. These adapters both have their own connectionpools. Using the same adapter might have caused different behavior. Using different datasources might make it possible to have more open incoming connections. This however in this case was most likely limited by other settings such as invoker threads.

Something to mind when considering the transaction related settings is it's impact on fault handling. This is however not the focus of this post. See for example; http://docs.oracle.com/cd/E15523_01/integration.1111/e10224/soa_transactions.htm and https://blogs.oracle.com/soabpm/entry/soa_suite_11g_-_transactions_b

Of course many other settings can be tuned to increase performance. The effects found when changing the settings might also differ with the nature of the process tested. In this case, invoker threads can be increased to allow messages to be picked up faster and the maximum number of connections allowed by the datasources can be increased. The process can also be made transient. The list of other possible options to make this specific process go faster are numerous. The purpose of this example is however to indicate the impact transactions can have on the performance of a process so the other factors are kept constant and the default settings from the Oracle SOA 11g PS5 image are used; http://www.oracle.com/technetwork/middleware/soasuite/learnmore/vmsoa-172279.html

You can download the used code and test results below;
https://dl.dropboxusercontent.com/u/6693935/blog/Transactions.zip

Oracle BPEL and Java. A comparison of different interaction options

When building BPEL processes in Oracle SOA Suite 11g, it sometimes happens some of the required functionality can't easily be created by using provided activities, flow control options and adapters. Often there are Java libraries available which can fill these gaps. This blog post provides an evaluation of different options for making interaction between BPEL and Java possible.

In the examples provided in this post, I'm using the JsonPath library (https://code.google.com/p/json-path/) inside a BPEL process. A usecase for this could be that a webclient calls the BPEL process with a JSON message and BPEL needs to extract fields from that message.

The Java code to execute, is the following;

package ms.testapp;

import com.jayway.jsonpath.JsonPath;

public class JsonPathUtils {
   
    public String ExecuteJsonPath(String jsonstring, String jsonpath) {
        String result = JsonPath.read(jsonstring, jsonpath).toString();
        return result;
    }
    
    public JsonPathUtils() {
        super();
    }
}

Of course small changes were necessary for the specific integration methods. I provided code samples at the end of this post for every method.

Integration methods

Embedding

Oracle has provided an extension activity for BPEL which allows Java embedding. By using this activity, Java code can be used directly from BPEL. The JsonPath libraries to use in the embedding activity can be put in different locations such as the domain library directory or be deployed as part of the BPEL process. Different classloaders will be involved. To check whether this matters I've tried both locations.

Performance
The Java call happens within the same component engine. Below are measures from when using JSON libraries deployed as part of the BPEL process (in SCA-INF/lib).

Below are measures from when putting the libraries in the domain library folder.

As you can see from the measures, the performance is very comparable. The location where the BPEL process gets it's classes from has no clear measurable consequences for the performance.

Re-use potential
When the libraries are placed in the domain lib folder, they can be reused by almost everything deployed on the applications server. This should be considered. When deploying as part of the composite, there is no re-use potential outside the composite except possibly indirectly by calling the composite.

Maintenance considerations
Embedded Java code is difficult to maintain and debug. When deployed as part of a BPEL process, changes to the library require redeployment of the process. When libraries are put in the domain library directory, changes to it, impact all applications using it and might require a restart.

XPath

XPath extension functions can be created and used in BPEL (+ other components) and JDeveloper. This is nicely described on; https://blogs.oracle.com/reynolds/entry/building_your_own_path.

Performance
The custom XPath library is included as part of the SOA infrastructure and does not leave this context. As can be seen, the performance is comparable to the Java embedding method.

Re-use potential
The reuse potential is high. The custom XPath library can be used in different locations/engines, dependent on the descriptor file.

Maintenance considerations
Reuse by different developers in JDeveloper requires minimal local configuration, but this allows GUI support of the custom library. There are no specific changes to BPEL code thus low maintenance overhead. Changing the library on the application server requires a restart of the server.

Spring component

The Java code can be called as a Spring component inside a composite. Here another component within the composite is called. The Java code is executed outside the BPEL engine.

Performance

Re-use potential
The following blog posts links to options with the Spring Framework; https://blogs.oracle.com/rammenon/entry/spring_framework_samples. When deployed inside a composite, reuse is limited to the composite. It is possible to define global Spring beans however, increasing re-use. The code can be created/debugged outside an embedding activity.

Maintenance considerations
The Spring component is relatively new to Oracle SOA Suite so some developers might not know how to work with this option yet. It's maintenance options are better then for the BPEL embedding activity. It is however still deployed as part of a composite.

External webservice

Java code can be externalized completely by for example providing it as a JAX-WS webservice or an EJB.

Performance
Performance is poor compared to the solutions described above. This is most likely due to the overhead of leaving soa-infra and the layers the message needs to pass to be able to be called from BPEL.

Re-use potential
Re-use potential is highest for this option. Even external processes can call the webservice so re-use is not limited to the same application server.

Maintenance considerations
Since the code is completely externalized, this option provides the best maintenance options. It can be developed separately by Java developers and provided to be used by an Oracle SOA developer. Also it can be replaced without requiring a server restart or composite redeploy.

Conclusion

The technical effort required to implement the different methods is comparable. Depending on the usecase/requirements, different options might be relevant. If performance is very important, embedding and XPath expressions might be your best choice. If maintenance and reuse are important, then externalizing the Java code in for example an external webservice might be the better option.

Summary of results

This is of course a personal opinion.

The used code with examples of all embedding options can be downloaded here; https://dl.dropboxusercontent.com/u/6693935/blog/TestJavaEmbedding.zip

How to deal with services that don't support concurrency? Offer requests one at a time.

When developing service orchestrations using Oracle SOA Suite, an often encountered problem is dealing with unreliable services. This can be services which cannot handle multiple simultaneous requests (don't support concurrency) or don't have an 100% availability (usually due to nightly batches or scheduled maintenance). One way to work with these services is having a good error handling or retry mechanism in place. For example, I've previously described a fault handling mechanism based on using Advanced Queues (AQ); http://javaoraclesoa.blogspot.nl/2012/06/exception-handling-fault-management-and.html. Using this mechanism, you can maintain the order of processing for messages and retry faulted messages. It would however be better if we can avoid faults. In case a service does not support concurrency (because of for example platform limitations or statefulness), messages will have to be offered one at a time.

If the service has a quick response time, you can make a process picking up messages from a AQ, synchronous and thus have only one running process at a time. This has been described at; http://mazanatti.info/index.php?/archives/81-SOA-Suite-11g-truly-singleton-with-AQ-Adapter.html. It's a recommended read.

In this blog post I'll describe a mechanism which can be used if a synchronous solution would not suffice, for example in long running processes. The purpose of this blog post is to illustrate a mechanism and it's components. It should not be used as-is in a production environment but tweaked to business requirements.

Implementation

I'll describe a database based mechanism which consists of several components;
- A database table holding process 'state'. In this example, CREATED, ENQUEUED, RUNNING, DONE
- A DBMS_SCHEDULER job which polls for changes. In my experience this is more stable then using the DbAdapter to do the same.
- A priority AQ to offer messages to BPEL in a specific order and allow loose coupling/flexibility/error handling mechanisms. In my experience this is very reliable.
- A BPEL process consuming AQ messages and calling the service which doesn't support concurrency. There should be only one running instance of this process at a time.

I've created a process state table which holds the process states and provides state history. I've also created a view on this table which only displays the current state. There is a column in the table PROC_NAME. This corresponds to the subscriber used in the BPEL process.

A database job polls for records every minute with state CREATED. If found and no other processes are in state ENQUEUED or RUNNING, a new message is enqueued. I've split the states ENQUEUED and RUNNING to be able to identify which messages have been picked up by the BPEL process and which haven't. There should only be one process in state RUNNING at a time.

I've created a simple HelloWorld BPEL process. This process polls for messages on the AQ. It picks up the message and informs the database that it has picked up a message (set the state to RUNNING). Next I've stubbed calling a service with a wait of one minute. After the period is over, the state is set to DONE. The process looks as followed;

At the end of this post you can download the code. To run the example however, the database needs to have a user TESTUSER with the correct grants to alllow queueing/dequeueing (see supplied script). Also in Weblogic server, there needs to be a JDBC datasource configured and a connection factory (eis/AQ/testuser) defined in the AqAdapter. You can find an example for configuring the DbAdapter at http://kiransaravi.blogspot.nl/2012/08/configuring-dbadapter-its-datasource.html. Configuration for the AqAdapter is very similar.

Running the example

First you need to create the table, trigger, AQ, package, DBMS_SCHEDULER job. This can be done by executing the supplied script.

To start testing the mechanism you can execute the following;

begin

insert into ms_process(proc_name,proc_comment) values('HELLOWORLD','Added new record for test 1');

insert into ms_process(proc_name,proc_comment) values('HELLOWORLD','Added new record for test 2');

insert into ms_process(proc_name,proc_comment) values('HELLOWORLD','Added new record for test 3');

commit;

end;

This will insert 3 records in the process table. These messages will be picked up in order. For implementations in larger applications I recommend using the PROC_SEQ field in the process table to obtain  required information for processing.

After a couple of minutes, you can see the following in the process state table;

As you can see, the messages were created at approximately the same time. The messages are picked up in order of insertion (based on ProcessId). Also as can be seen from the table, when a process is running (the period between state RUNNING and DONE), no other processes are running; there is no overlap in time.

After processing, the process view indicates the latest process state for every process. All processes are done.

In the Enterprise Manager, three processes have been executed and completed.

AQ in a clustered environment

In a clustered environment you have to mind that in an 11.2 database, AQ messages can be picked up twice from the same queue under load. Since this would break the mechanism, I suggest taking the below described workaround.

Bug: 13729601
Added: 20-February-2012
Platform: All
The dequeuer returns the same message in multiple threads in high concurrency environments when Oracle database 11.2 is used. This means that some messages are dequeued more than once. For example, in Oracle SOA Suite, if Service 1 suddenly raises a large number of business events that are subscribed to by Service 2, duplicate instances of Service 2 triggered by the same event may be seen in an intermittent fashion. The same behavior is not observed with a 10.2.0.5 database or in an 11.2 database with event10852 level 16384 set to disable the 11.2 dequeue optimizations.

Workaround: Perform the following steps:

    Log in to the 11.2 database:
    CONNECT /AS SYSDBA

    Specify the following SQL command in SQL*Plus to disable the 11.2 dequeue optimizations:
    SQL> alter system set event='10852 trace name context forever,
    level 16384'scope=spfile;

Considerations

The mechanism described can be used to avoid parallel execution of processes. Even when the processes are long running and synchronous execution is not an option.

Polling

The mechanism contains polling components; the DBMS_SCHEDULER job and the AqAdapter. This has two major drawbacks;
- it will cause load even when the system is idle
- it allows a period between finishing of a process and starting of the next process

You could consider starting the BPEL process actively from the database (thus avoiding polling) by using for example UTL_DBWS (see for example http://orasoa.blogspot.nl/2006/11/calling-bpel-process-with-utldbws.html). This however requires that the URL of the BPEL process is known in the database and that the ACL (Access Control List) is configured correctly. Also error handling should be reconsidered. The overhead of polling is minor. If a delay of 1 minute + default AqAdapter polling frequency is acceptable, a solution based on the described mechanism can be considered. Also, the DBMS_SCHEDULER job polling frequency can be reduced and the AqAdapter polling behavior can be tweaked to reduce the lost time between polls.

Chaining

Ending the process with a polling action -> initiation of the next message is not advisable since it raises several new questions;
- what to do if there are no messages waiting? having a polling mechanism together with this mechanism might break the 'only one process is running at the same time'-rule
- what to do in case of errors -> when the chain is broken

Retiring/activating

I've tried a mechanism which would retire a process at the start and then reactivate it after completion. This would disallow more then one process to be running at the same time. This appeared not to be a solid mechanism. Retiring and activating a process takes time in which new messages could be picked up. Also using the Oracle SOA API during process execution adversely effects performance.

Efficiently determining the current state

I've not tested this solution with large number of processes. I think in that case I should reconsider on how to keep a process history and get to the current state efficiently in a polling run. Most likely I'd use two tables. One for the current state which I would update and another separate table for the history which I would fill with PL/SQL triggers on the current state table.

Download

You can download the BPEL process here; https://dl.dropboxusercontent.com/u/6693935/blog/HelloWorldAQProcState.zip

The databasecode can be downloaded here (you might want to neatify it if for example you like CDM);
https://dl.dropboxusercontent.com/u/6693935/blog/processstate.txt

Cleaning up unused namespaces in Oracle SOA 11g BPEL processes by using a Python script

Composites are often created and after creating, they are changed/expanded to implement functionality or bugfixes. When adding new partnerlinks and variables, removing them, importing new XSD, removing them, etc it often occurs that there are namespace definitions inside for example BPEL processes which are no longer relevant because they are not used anymore. This can adversely effect performance/memory usage and increases the chance of errors when XSD's are changed, removed or added (such as inconsistent duplicate namespace definitions).

I took this issue as a nice opportunity to learn myself a bit of Python. Python is a popular scripting language (http://www.tiobe.com/index.php/content/paperinfo/tpci/index.html) and used by several software vendors such as Oracle for Weblogic server; WLST (http://docs.oracle.com/cd/E14571_01/web.1111/e13813/quick_ref.htm) and ESRI (http://esripress.esri.com/display/index.cfm?fuseaction=display&websiteID=224&moduleID=0) for GIS related programming. There is an official Python tutorial available on http://docs.python.org/3.3/tutorial/ and I've also used http://www.vogella.com/articles/Python/article.html to learn some basics.

First impression of the Python language
- I like the usage of indentation compared to the use of brackets or end statements
- code completion is far from perfect with the PyDev plug-in for Eclipse when using Python 3.3 (I needed to Google a lot for API documentation)
- even without background in Python, you can quickly get something working after reading some tutorials (although I have some experiences with other scripting languages like PERL, PHP, JavaScript. I usually use PERL for my regular scripting needs).

Implementation

Purpose

I wanted to create a script which would cleanup unused namespaces in XML files. I started with a BPEL file as an example (the same example as used in; http://javaoraclesoa.blogspot.nl/2013/04/soa-suite-ps6-11117-service-loose.html. It can be downloaded here; https://dl.dropboxusercontent.com/u/6693935/blog/HelloWorldTokens.zip). The BPEL file had the following contents;

<?xml version = "1.0" encoding = "UTF-8" ?>
<!--
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
  Oracle JDeveloper BPEL Designer
 
  Created: Thu Apr 11 12:23:10 CEST 2013
  Author:  Maarten
  Type: BPEL 2.0 Process
  Purpose: Synchronous BPEL Process
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
-->
<process name="CallHelloWorld"
               targetNamespace="http://xmlns.oracle.com/HelloWorld/CallHelloWorld/CallHelloWorld"
               xmlns="http://docs.oasis-open.org/wsbpel/2.0/process/executable"
               xmlns:client="http://xmlns.oracle.com/HelloWorld/CallHelloWorld/CallHelloWorld"
               xmlns:ora="http://schemas.oracle.com/xpath/extension"
               xmlns:bpelx="http://schemas.oracle.com/bpel/extension"
         xmlns:bpel="http://docs.oasis-open.org/wsbpel/2.0/process/executable"
         xmlns:ns1="http://xmlns.oracle.com/HelloWorld/HelloWorld/HelloWorld">

    <import namespace="http://xmlns.oracle.com/HelloWorld/CallHelloWorld/CallHelloWorld" location="CallHelloWorld.wsdl" importType="http://schemas.xmlsoap.org/wsdl/"/>
    <!--
      ////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
        PARTNERLINKS                                                     
        List of services participating in this BPEL process              
      ////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
    -->
  <partnerLinks>
    <!--
      The 'client' role represents the requester of this service. It is
      used for callback. The location and correlation information associated
      with the client role are automatically set using WS-Addressing.
    -->
    <partnerLink name="callhelloworld_client" partnerLinkType="client:CallHelloWorld" myRole="CallHelloWorldProvider"/>
    <partnerLink name="HelloWorld" partnerLinkType="ns1:HelloWorld"
                 partnerRole="HelloWorldProvider"/>
  </partnerLinks>

  <!--
    ////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
      VARIABLES                                                       
      List of messages and XML documents used within this BPEL process
    ////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
  -->
  <variables>
    <!-- Reference to the message passed as input during initiation -->
    <variable name="inputVariable" messageType="client:CallHelloWorldRequestMessage"/>

    <!-- Reference to the message that will be returned to the requester-->
    <variable name="outputVariable" messageType="client:CallHelloWorldResponseMessage"/>
    <variable name="InvokeHelloWorld_process_InputVariable"
              messageType="ns1:HelloWorldRequestMessage"/>
    <variable name="InvokeHelloWorld_process_OutputVariable"
              messageType="ns1:HelloWorldResponseMessage"/>
  </variables>

  <!--
    ////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
     ORCHESTRATION LOGIC                                              
     Set of activities coordinating the flow of messages across the   
     services integrated within this business process                 
    ////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
  -->
  <sequence name="main">

    <!-- Receive input from requestor. (Note: This maps to operation defined in CallHelloWorld.wsdl) -->
    <receive name="receiveInput" partnerLink="callhelloworld_client" portType="client:CallHelloWorld" operation="process" variable="inputVariable" createInstance="yes"/>
    <assign name="Assign1">
      <copy>
        <from>$inputVariable.payload/client:input</from>
        <to>$InvokeHelloWorld_process_InputVariable.payload/ns1:input</to>
      </copy>
    </assign>
    <invoke name="InvokeHelloWorld"
            partnerLink="HelloWorld" portType="ns1:HelloWorld"
            operation="process"
            inputVariable="InvokeHelloWorld_process_InputVariable"
            outputVariable="InvokeHelloWorld_process_OutputVariable"
            bpelx:invokeAsDetail="no"/>
    <assign name="Assign2">
      <copy>
        <from>$InvokeHelloWorld_process_OutputVariable.payload/ns1:result</from>
        <to>$outputVariable.payload/client:result</to>
      </copy>
    </assign>
    <!-- Generate reply to synchronous request -->
    <reply name="replyOutput" partnerLink="callhelloworld_client" portType="client:CallHelloWorld" operation="process" variable="outputVariable"/>
  </sequence>
</process>

The ora namespace was not used in this process but it is specified in the process tag.

lxml

Based on the following I started with lxml; http://lxml.de/api/lxml.etree-module.html#cleanup_namespaces since it had a function to easily clean unused namespaces and on StackOverflow.com there were many posts on lxml. To install lxml for Windows, I had to first download and install it from http://www.lfd.uci.edu/~gohlke/pythonlibs/#lxml

cleanup_namespaces

My first try was the following;

import lxml.etree as et
filename_in='D:\\dev\\HelloWorld\\CallHelloWorld\\CallHelloWorld.bpel'
filename_out='D:\\dev\\HelloWorld\\CallHelloWorld\\CallHelloWorld.bpel.out'
tree = et.parse(filename_in)
et.cleanup_namespaces(tree)
tree.write(filename_out)

In the output I noticed that although my process definition had been cleaned up from

<process name="CallHelloWorld"
               targetNamespace="http://xmlns.oracle.com/HelloWorld/CallHelloWorld/CallHelloWorld"
               xmlns="http://docs.oasis-open.org/wsbpel/2.0/process/executable"
               xmlns:client="http://xmlns.oracle.com/HelloWorld/CallHelloWorld/CallHelloWorld"
               xmlns:ora="http://schemas.oracle.com/xpath/extension"
               xmlns:bpelx="http://schemas.oracle.com/bpel/extension"
         xmlns:bpel="http://docs.oasis-open.org/wsbpel/2.0/process/executable"
         xmlns:ns1="http://xmlns.oracle.com/HelloWorld/HelloWorld/HelloWorld">

To

<process xmlns="http://docs.oasis-open.org/wsbpel/2.0/process/executable" xmlns:bpelx="http://schemas.oracle.com/bpel/extension" name="CallHelloWorld" targetNamespace="http://xmlns.oracle.com/HelloWorld/CallHelloWorld/CallHelloWorld">

Several namespaces were removed which were in use such as the ns1 namespace which was used in a partnerlink definition as part of an attribute value;

<partnerLink name="HelloWorld" partnerLinkType="ns1:HelloWorld" partnerRole="HelloWorldProvider"/>

My conclusion was that using prebuild functions like the above would most likely not help solve this problem. I did not find a way to limit the functionality to specific namespaces.

Determining used namespaces

I tried a different approach; determine namespaces used in the root element and try to find them on different locations in the BPEL file. Then rewriting the root element.

import lxml.etree as et
import copy
filename_in='D:\\dev\\HelloWorld\\CallHelloWorld\\CallHelloWorld.bpel'
filename_out='D:\\dev\\HelloWorld\\CallHelloWorld\\CallHelloWorld.bpel.out'
tree = et.parse(filename_in)
root=tree.getroot()
nsmap=root.nsmap
nsmapnew= copy.deepcopy(nsmap)

#print (nsmap.values())
namespaces=set(nsmap.values())
print ("Namespaces found: "+str(len(nsmap)))
for nsitem in nsmap:
    found=0
    nscount=0;
    if nsitem != None:
        print ("Processing prefix: "+nsitem+" Namespace: "+nsmap.get(nsitem))
        #processing all elements
        walkAll = tree.getiterator()
        for elt in walkAll:
            #check element
            eltns=elt.xpath('namespace-uri(/*)')
            if eltns==nsmap.get(nsitem):
                found=1
                #print("Found namespace as element namespace")
                break
            if str(elt.text).find(nsitem+":") != -1:
                found=1
                #print("Found prefix as part of element text")
                break
            #check attributes
            for attribute in elt.attrib:
                if attribute.startswith("{"+nsmap.get(nsitem)+"}"):
                    found=1
                    #print ("Found namespace as attribute name namespace")
                    break
                if str(elt.attrib[attribute]).find(nsitem+":") != -1:
                    found=1
                    #print ("Found prefix as part of attribute value")
                    break
    else:
        #default namespace not removing
        found=1
    if found==0:
        print("Not found")
        del nsmapnew[nsitem]
print ("Namespaces remaining: "+str(len(nsmapnew)))

new_root = et.Element(root.tag, attrib=root.attrib,nsmap=nsmapnew)
new_root[:] = root[:]

#to add the top level comment
try:
    firstcomment=root.getprevious()
    new_root.addprevious(firstcomment)
except:
    None

tree = et.ElementTree(new_root)

tree.write(filename_out, xml_declaration=True, encoding='utf-8',pretty_print=True) 

This rewrote my XML like I wanted it to. Even if namespaces were used in XPATH expressions in the BPEL file, they were being recognized as being used. One drawback however was the namespace prefix which was added for all the subelements even though these elements were in the default namespace. I considered using a transformation to fix this. This would however remove the comments from the file so I decided not to. The script output was as followed;

Namespaces found: 6
Processing prefix: ns1 Namespace: http://xmlns.oracle.com/HelloWorld/HelloWorld/HelloWorld
Processing prefix: ora Namespace: http://schemas.oracle.com/xpath/extension
Not found
Processing prefix: client Namespace: http://xmlns.oracle.com/HelloWorld/CallHelloWorld/CallHelloWorld
Processing prefix: bpel Namespace: http://docs.oasis-open.org/wsbpel/2.0/process/executable
Processing prefix: bpelx Namespace: http://schemas.oracle.com/bpel/extension
Namespaces remaining: 5

The created output file was as followed;

<?xml version='1.0' encoding='UTF-8'?>
<!--
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
  Oracle JDeveloper BPEL Designer
 
  Created: Thu Apr 11 12:23:10 CEST 2013
  Author:  Maarten
  Type: BPEL 2.0 Process
  Purpose: Synchronous BPEL Process
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
-->
<bpel:process xmlns:ns1="http://xmlns.oracle.com/HelloWorld/HelloWorld/HelloWorld" xmlns:client="http://xmlns.oracle.com/HelloWorld/CallHelloWorld/CallHelloWorld" xmlns:bpel="http://docs.oasis-open.org/wsbpel/2.0/process/executable" xmlns:bpelx="http://schemas.oracle.com/bpel/extension" xmlns="http://docs.oasis-open.org/wsbpel/2.0/process/executable" name="CallHelloWorld" targetNamespace="http://xmlns.oracle.com/HelloWorld/CallHelloWorld/CallHelloWorld"><bpel:import namespace="http://xmlns.oracle.com/HelloWorld/CallHelloWorld/CallHelloWorld" location="CallHelloWorld.wsdl" importType="http://schemas.xmlsoap.org/wsdl/"/>
    <!--
      ////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
        PARTNERLINKS                                                     
        List of services participating in this BPEL process              
      ////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
    -->
  <bpel:partnerLinks>
    <!--
      The 'client' role represents the requester of this service. It is
      used for callback. The location and correlation information associated
      with the client role are automatically set using WS-Addressing.
    -->
    <bpel:partnerLink name="callhelloworld_client" partnerLinkType="client:CallHelloWorld" myRole="CallHelloWorldProvider"/>
    <bpel:partnerLink name="HelloWorld" partnerLinkType="ns1:HelloWorld" partnerRole="HelloWorldProvider"/>
  </bpel:partnerLinks>

  <!--
    ////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
      VARIABLES                                                       
      List of messages and XML documents used within this BPEL process
    ////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
  -->
  <bpel:variables>
    <!-- Reference to the message passed as input during initiation -->
    <bpel:variable name="inputVariable" messageType="client:CallHelloWorldRequestMessage"/>

    <!-- Reference to the message that will be returned to the requester-->
    <bpel:variable name="outputVariable" messageType="client:CallHelloWorldResponseMessage"/>
    <bpel:variable name="InvokeHelloWorld_process_InputVariable" messageType="ns1:HelloWorldRequestMessage"/>
    <bpel:variable name="InvokeHelloWorld_process_OutputVariable" messageType="ns1:HelloWorldResponseMessage"/>
  </bpel:variables>

  <!--
    ////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
     ORCHESTRATION LOGIC                                              
     Set of activities coordinating the flow of messages across the   
     services integrated within this business process                 
    ////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
  -->
  <bpel:sequence name="main">

    <!-- Receive input from requestor. (Note: This maps to operation defined in CallHelloWorld.wsdl) -->
    <bpel:receive name="receiveInput" partnerLink="callhelloworld_client" portType="client:CallHelloWorld" operation="process" variable="inputVariable" createInstance="yes"/>
    <bpel:assign name="Assign1">
      <bpel:copy>
        <bpel:from>$inputVariable.payload/client:input</bpel:from>
        <bpel:to>$InvokeHelloWorld_process_InputVariable.payload/ns1:input</bpel:to>
      </bpel:copy>
    </bpel:assign>
    <bpel:invoke name="InvokeHelloWorld" partnerLink="HelloWorld" portType="ns1:HelloWorld" operation="process" inputVariable="InvokeHelloWorld_process_InputVariable" outputVariable="InvokeHelloWorld_process_OutputVariable" bpelx:invokeAsDetail="no"/>
    <bpel:assign name="Assign2">
      <bpel:copy>
        <bpel:from>$InvokeHelloWorld_process_OutputVariable.payload/ns1:result</bpel:from>
        <bpel:to>$outputVariable.payload/client:result</bpel:to>
      </bpel:copy>
    </bpel:assign>
    <!-- Generate reply to synchronous request -->
    <bpel:reply name="replyOutput" partnerLink="callhelloworld_client" portType="client:CallHelloWorld" operation="process" variable="outputVariable"/>
  </bpel:sequence>
</bpel:process>

The ora namespace had been removed from the process attribute. The file had also become smaller. The ora namespace is however a default Oracle namespace and to make sure I didn't break anything, I tried to compile and deploy the altered process. This was successful. I also tested it with more complex processes. The resulting BPEL file was still fully functional.

Possible followups could be;

- expand the script and allow recursively processing of multiple files and filetypes
- determine used and unused namespaces for every element, not just the root element
- link the results of unused namespaces within a project to XSD's which could also be removed from a project if they are not used by any file in the project
- if for example problems start to occur with XPATH expressions after removing default Oracle namespaces; exclude specific namespaces from cleaning
- remove the namespace prefix for the default namespace

Using PL/SQL object types to get nested or 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.

Implementation

Database code

The below code shows how a repeating structure can be implemented using a PL/SQL object type;

CREATE OR REPLACE TYPE ITEM
IS
  OBJECT
  (
    NAME  VARCHAR2(255) ,
    VALUE VARCHAR2(255) );

CREATE OR REPLACE
TYPE         ITEM_ARR AS TABLE OF ITEM

The PL/SQL type ITEM_ARR contains an array of PL/SQL objects; ITEM. The object ITEM contains two fields; NAME and VALUE.

To illustrate the usage of the ITEM_ARR type, a sequence, table, trigger are created and a small TAPI (table API) package. The trigger is optional for this example.

CREATE OR REPLACE TRIGGER ITEMS_TAB_BI 
BEFORE INSERT ON ITEMS_TAB
FOR EACH ROW
BEGIN
  IF (:NEW.ID IS NULL) THEN
    SELECT ITEMS_TAB_SEQ.NEXTVAL INTO :NEW.ID FROM DUAL;
  END IF;
END;

CREATE OR REPLACE
PACKAGE BODY ITEMS_TAB_UTILS AS
  PROCEDURE ADD_ITEMS(P_ITEMS ITEM_ARR) AS
  BEGIN
    FORALL x in P_ITEMS.First..P_ITEMS.Last
     INSERT INTO ITEMS_TAB (ID,NAME,VALUE) VALUES (ITEMS_TAB_SEQ.NEXTVAL,P_ITEMS(x).NAME,P_ITEMS(x).VALUE);
  END ADD_ITEMS;
END ITEMS_TAB_UTILS;

To use an object type from another schema, the connecting schema needs to have execute permission on the object type.

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;

The code can be downloaded here; https://dl.dropbox.com/u/6693935/blog/ItemUtils.zip

Considerations

PL/SQL object types can be used to get repeated or nested structures to the database in an easy and performing way. There are some things to mind though when using PL/SQL object types.

Datamodel changes

The datamodel is defined in XML schema's in the BPEL process and also in the database definition of the object type. When adding a field or changing the order of fields, the database code and the BPEL code need changing. You won't get errors in every situation when you don't do it right, since object type fields are passed in order and not by name. This is a thing to mind when making changes.

Object types in tables and queues

Object types can be used as a type in a database table or a queue message type. My suggestion is not to use object types this way. The below error message says much.

ORA-02303: cannot drop or replace a type with type or table dependents
02303. 00000 -  "cannot drop or replace a type with type or table dependents"
*Cause:    An attempt was made to drop or replace a type that has dependents.
           There could be a substitutable column of a supertype of the type
           being dropped.
*Action:   Drop all type(s) and table(s) depending on the type, then retry
           the operation using the VALIDATE option, or use the FORCE option.

If the object type changes, you will have to consider migration strategies of existing tables and queues. This can be troublesome. Sometimes tables need to be dropped and recreated because an alter type might not be sufficient to achieve the desired change. In a production environment, if the PL/SQL code has not been written in a modular way, making sure the queue is empty and will remain empty during the installation, can be a bother.

Also when a complex datamodel is implemented using PL/SQL object types, the dependencies between the types can become difficult to manage in case of changes. This is illustrated in my example by the following;  I can't CREATE OR REPLACE the ITEM type because it is used in the ITEM_ARR type.

Conclusion

Object types can be used to increase performance and decrease complexity, however changes are more difficult to implement. My suggestion on this is to only use them in the following cases;

- when using them allows you to avoid complex BPEL code
- when performance is important and you want to minimize DbAdapter calls and transformations
- when changes to the data structure implemented in PL/SQL object types, are rare

Don't use them as a column in a table or queue. You are more flexible by using other methods such as using primitive types and spreading the data structure over several tables or by using XMLType or LOB data types.