Advanced Logic in Oracle BPEL Polling Database Adapter

(Or Database Adapter Mungery for Fun and Profit)

Okay, so this is going to be a technical post about strategies to enable more complex logic in Oracle Database Adapter services via a couple of different approaches including custom SQL. It assumes that the reader is reasonably familiar with (and interested in) the principles of Service Oriented Architecture (SOA) and use of the Oracle Fusion Middleware stack. If you don’t fall into this category – run! While you still can.

5-11-2009 8-45-28 AMIf you’ve had any experience with Oracle BPEL Database adapters, you’ll know that they come in four different basic flavours:

  1. Create, Read, Update, Delete (CRUD) – offering basic editing and selection functionality
  2. Custom SQL – Allows specification of a limited custom SQL statement
  3. Stored Procedure – Executes a stored database procedure/function
  4. Polling – Polls a database for changes meeting a specified selection criteria. Polling adapters must also perform an action on selection to prevent the same set of table data from being reselected – there are various supported strategies for this including:
    • Delete the rows that have been read – the most basic, but not great practice if your database is also providing an audit trail
    • Update a field in the selected rows that will make the row no longer match the selection criteria (also known as logical delete) – such as changing a state field from ready to processed
    • Updating a sequence table or file

This post will focus on the fairly common pattern where a database adapter is polling a table and triggering a BPEL process in response to a specific change, such as a change in a state field, and then performing a logical delete.

If you have used a polling adapter before, you will be aware that the selection criteria is quite limited. While the wizard appears to generate an SQL select statement it’s logic is actually based on a Toplink mapping file, with some additional configuration specified in the WSDL binding. This means that the selection logic used in the adapter is limited largely to the options provided by the wizard, which raises issues such as:

  • If the selection criteria is made over a join, data from both tables must be returned by the poller – if you have a situation where there is a one to many relationship between the two table you can end up with the desired information from the first table plus a few thousand instances of the foreign key from the second – nasty and wasteful of both time and bandwidth.
  • The selection criteria cannot call stored procedures or built in functions (like sysdate, count, sum etc.)

In practice, I have found this quite limiting. What follows is a couple of strategies to get more complex logic in your database adapters:

Push the logic into the Database Layer

In the case where selection is over a single table, but some functionality is required that is not supported by the adapter (such as use of sysdate or sum), the selection logic can be abstracted into the database layer as criteria for a database view (which is itself essentially a database side SELECT statement). The poller can then be configured to just select all rows from the view.

Unfortunately, this approach is limited to fairly basic cases – a logical delete poller for instance will need to lock records for update (as per the SELECT FOR UPDATE SQL construct) and views with more complex logic that select over joins cannot be locked in this way.

Custom SQL

This is a more complicated approach, but as stated above is necessary for more complex cases such as where the query is over a join. JDeveloper offers an interface to the Toplink descriptor file which allows you to use some custom SQL.

The logical delete polling strategy is too complex for the custom SQL, so we will need to be a bit sneaky here and take a basic delete polling strategy and bend it to perform an effective logical delete (or a logical logical delete if you’re into that sort of thing).

  1. The first step is to create the poller with a basic delete strategy (use the desired return fields but just the default selection logic for now). When you get to the last screen of the wizard where the representative SQL is displayed, copy the SQL statement before the WHERE clause to reuse in the selection component of your custom SQL (so that the Toplink mappings will still line up)5-11-2009 8-58-32 AM
  2. Next we will enter the Custom SQL query:
    • In JDeveloper you click on the project then select Application Sources>Toplink>your_poller_name_here
    • This will open a form Toplink descriptor tab. Change from the Application navigation pane to the Structure pane. Open the package and select the root table of the query.5-11-2009 9-00-02 AM
    • This will change the descriptor to one specific for that table. Select the Queries tab and the Named Queries sub tab. In that tab select the named query referenced in the poller WSDL (will probably be the same name as the poller). Then select the Format sub tab and change type from Expression to SQL and enter the custom SQL in the space provided.5-11-2009 9-01-37 AM
  3. Now we will use more custom SQL to make the delete behave as a logical delete:
    • Change the subtab from Named Queries to Custom SQL and select the Delete Tab. You can now enter a custom SQL UPDATE statement which will be performed at the delete stage of the poll (such as setting a status field to processed) thereby actually effecting a logical delete.5-11-2009 9-02-24 AM
  4. The final step is to reopen the partner link for the adapter and edit it – progressing (at least) until you can select the option to Finish. This forces regeneration of the relevant Toplink mappings files with the custom SQL handling.

Some things to keep in mind when formulating the custom SQL:

  • It looks like both the SQL and some of the Toplink handling is executed, which can lead to conflicts if the select statement tries to do something that the Toplink mapping is already doing (locking for instance).
  • Adapter errors will usually generate output in the default opmn logs on the application server, and result in the BPEL engine changing turning the BPEL driven by the adapter off to prevent an infinite failure loop.
  • If the adapter doesn’t throw and error, it may still not like your selection SQL. In this case, it appears to fall back on a SELECT ALL named query – so if your poller is just returning everything in the table and ignoring the selection criteria it’s probably a non-fatal conflict between Toplink and the SQL.
  • I also was unable to make the custom SELECT logic work with the Distributed Polling option checked in the wizard, so this option may not be appropriate in cases where this is required.

Directly Munge the Toplink Descripters

There are some Oracle workarounds that describe directly editing the Toplink descriptors. In most cases I would advise against this unless specifically advised by Oracle as the underlying implementation logic is not particularly transparent and this cam lead to unexpected effects. Caveat Emptor.


If you have any questions, please feel free to leave them in the comments and I will try to address them as best I can.



About this entry