3 Replies Latest reply: May 16, 2012 2:04 PM by Arik RSS

    how to specify custom SQL in polling db adapter with logical delete option

      Hi all,

      I am writing a SOA composite app using JDeveloper SOA Suite connecting to a SQL Server db using a polling DB Adapter with the logical delete option to send data to a BPEL process.

      I have requirements which go beyond what is supported in the JDeveloper UI for DB Adapter polling options, namely:
      * update more than one column to mark each row read, and
      * specify different SQL for the logical delete operation based on whether bpel processing of the data polled was successful or not.
      A complicating factor is that the polling involves two tables. Here is my full use-case:

      1) Polling will select data derived from two tables: e.g. 'headers' and 'details' simplified for this example:
      table: headers
      hid - primary key
      name - data label
      status - 'unprocessed', 'processed', or 'error'
      processedDate - null when data is loaded, set to current datetime when row is processed
      table: details
      hid - foreign key pointed at header.hid
      attr - data attribute name
      value - value of data attribute

      2) There is a many:1 relationship between detail and header rows through the hid columns. The db adapter polling SELECT shall return results from an outer join consisting of one header row and the associated detail rows where header.status = 'unprocessed' and header.hid = details.hid. (This is supported by the Jdeveloper UI)

      3) The polled data will be sent to be processed by a bpel process:
      3.1) If the bpel processing succeeds, the logical delete (UPDATE) operation shall set header.status = 'processed', and header.processedDate = 'getdate()'.
      3.2) If bpel processing fails (e.g. hits a data error while processing the selected data) the logical delete (UPDATE) operation shall set header.status = 'failed', header.processedDate = 'getdate()', and header.errorMsg = '{some text returned from bpel}'.

      Several parts of #3 are not supported by the JDeveloper UI: updating multiple columns to mark the row processed, using getdate() to populate a value of one of those column updates, doing different update operations based on the results of the BPEL processing of the data (success or error), and using data obtained from BPEL processing as a value of those column updates (error message).

      I have found examples which describe specifying custom SQL using the polling delete option to create a template then modifying the toplink file(s) to specify custom select and update SQL to implement a logical delete. (e.g. http://dlimiter.wordpress.com/2009/11/05/advanced-logic-in-oracle-bpel-polling-database-adapter/ and http://myexperienceswithsoa.blogspot.com/2010/06/db-adapter-polling-tricks.html). But none of them match what I've got in my project, in the first case because maybe because I'm using a higher version of JDeveloper, and in the second I think because in my case two tables are involved.

      Any suggestions would be appreciated. Thanks, John