Building a Polling Staging Table for Oracle SOA Database Adapter

When deciding to use the Oracle SOA Database Adapter the use of a good poller friendly staging table makes life a bit easier.  It is also good to know a few things about how the database adapter works.

1. The adapter only invokes wizard generated SQL for polling purposes, no externally created SQL or PL/SQL can be leveraged.
2. The adapter builds the pre-polling, polling, and post-polling queries automatically based on fields and values chosen in the polling setup wizard.
3. In a clustered environment there will be a SOA DBAdapter application running and polling on each managed server in the cluster.  With multiple adapter polling, duplicate record processing and other problematic record processing scenarios can easily occur.
4. When using the Reserved Value feature/setting in a logical delete scenario, the database adapter must update source table that is being polled, with system specific data for a short period of time.  This means the column updated will need to be able to accommodate the system value used for the update.

Better Practices

After some time working with the database adapter I have encountered a number of different adapter usage scenarios and have tried a number of different approaches to discover some “better practices”.  I say “better practices” instead of “best practices” because I know how quickly the technology and solutions can change.

When using the SOA Database Adapter in a clustered environment:

1. Try to always create and use a staging table.  Poll against the staging table rather than polling against the original business data tables to decouple the business model data from the polling/processing control data.
2. Create the staging table so that it holds only the key pieces of business data as well as polling/processing data needed for polling purposes.  Having a staging Id column, a polling/processing status column, and a timestamp column in addition to key business data columns usually makes for a good staging table design for polling against. 
3. A staging table with a polling/processing status column decouples the polling/processing status tracking, management, and auditing from the business data that resides in non-staging tables.
4. Use only a single status column to manage both the polling status and processing status of a staged record.  I tried very hard to fight this practice and eventually lost.   I wanted to separate the polling status from the staged record processing status but the database adapter can only update a single column when finished polling, which lead to my many issues of managing the record  state and transactionality between the adapter and my BPEL process.  The better approach I have found is to use one status column to simplify the polling and process status management.
5.  The database adapter only allows a single column to be updated by its generated queries.  I had my poller looking at both the polling status and processing status to pick up valid records but the adapter was only capable of updating a single column, my polling status column.  I then made sure to update my processing status column in the beginning of my BPEL process, however system issues like a stuck thread, server crash, or transaction roll back left my record in states where the polling status and staging status could no longer be polled after all BPEL retries were unsuccessful.
6. When in a logical delete scenario in a clustered environment, Distributed Polling/Skip Locking cannot meet all polling needs.  In this case the Reserved Value setting will likely be used, meaning that the polling table must be modified by the poller with non-business system related values for record ownership purposes.    Using a staging table prevents the business table from being updated with the non-business related data.
7. Certain polling scenarios will lead to the need for polling against a database view instead of the staging table.   Utilizing a staging table allows for better flexibility in using a database view to help resolve concurrent polling, time sensitive polling, and record limit sensitive polling requirements.  You can find more information in my other blog post talking discussing a handy logical delete polling pattern.
8. When using the Reserved Value feature/setting in a logical delete scenario, the column updated by the database adapter will need to be of type “VARCHAR” and an approximate size of 20 or greater,  i.e. VARCHAR(20), to accommodate the values used by the poller to update the record with the system specific data.

Keep in mind that utilizing a staging table to poll against means that somehow the staged record data must be inserted into it the staging table.  Be sure to plan and design solutions appropriately to accommodate the loading of the staging table with data.

Remember that the practices listed are “better practices”.  Be sure to give them a shot to see what works for your situations, what does not, and to give yourself a head start on creating high quality solutions.  

Product Versions

• Oracle SOA Suite 11.1.1.5.0
• Oracle RDBMS 11.1.1.2.0 non RAC