Wednesday, December 7, 2011

Oracle SOA Suite 11g – Advanced Configurations for Database Adapter.

Oracle SOA Suite 11g – Advanced Configurations for Database Adapter.

This blog give the insight on the new features in Database Adapter configuration in Oracle SOA Suite 11g.

Query by Example:

A new Operation type 'Query by Example' has been introduced in Database Adapter Configuration. Query By Example does not require a selection criteria to be specified at design time like SELECT Operation. A selection criteria is inferred from an exemplary input XML record for each invoke. Use queryByExample when you do not want to create a query using the visual query builder and want the flexibility of allowing the input record to share the same XML schema as the output records. The queryByExample operation is will take little more time compared to SELECT operation because a new SELECT must be prepared for each execution.

Doing Synchronous Post to BPEL (Allow In-Order Delivery):

In this feature, the entire invocation is in a single thread and global transaction. By default, initiation is asynchronous and the BPEL process is invoked in a separate global transaction. With Oracle Mediator, it is generally a synchronous invoke so this is only specific to an Oracle BPEL process.

Advanced Options:

A new screen in Adapter configuration wizard for advanced options is added. This screen shows different options based on the type of operation selected. The below are the some of the advanced configurations.


We can set options like no. of retry attempts, interval between retries back-off factor and max interval values.

JDBC Options: 

This includes options like Query timeout value to specify the timeout for query execution.

Interaction Options: 

This includes various interaction options. Get Active UnitOfWork ( advanced setting that forces all invoke activities in the same global transaction to use the same SQL connection if going to the same database), Detect Omissions (allows the MERGE and INSERT operations to ignore empty or missing XML elements in the input payload), Optimize Merge (should always be set to true, as it is a general enhancement to MERGE performance)

Native Sequencing (Oracle only):

Allows specifying that the primary key will be assigned from a sequence on any insert.

Polling Options:

For Polling Options the below are the two new configuration parameters added.

Enable Streaming: 

This option is used to enable support to stream the payload. The payload is streamed to a database instead of getting manipulated in SOA run time as in a memory DOM. This feature is used while handling large payloads.

Schema Validation:

When set to true, all XML files produced by the polling Oracle Database Adapter (for Receive activities) is validated against the XSD file. On failure, the XML record is rejected but still marked as processed by the Oracle Database Adapter.
More detailed SQL queries display with sections for Polling SQL and After Read SQL


  1. red scope studios Web Design is a Sugar Land and Houston Web Design / graphic design company specializing in web design, web development, database design, custom database development, content management systems, and ecommerce websites.

  2. Doing Synchronous Post to BPEL (Allow In-Order Delivery)

    what about spring beans?

  3. When using the '?' in the where clause, is there a way to tell db adapter to use "is NULL" in the where clause when the input value is empty instead of "= NULL"?

    Consider in the pure sql jca: select c2 from t1 where c1=?

    with the table t1:

    In the case where c1 is empty(not assigned or NULL-ed/xsi:nil="true"-ed) the dbadapter returns nothing In the case where c1 is x the dbadapter returns 1 row with value b

    Is this due to the db adapter not using 'c1 is null' in it's where clause but instead 'c1 = null' to replace 'c1 = ?'?

    How to tell the db adapter to detect when c1's value is empty to use the 'c1 is null' where clause instead of 'c1 = null'?