Siva's Blog

In this post, we will have a look at another typical use case of polling DB View using DB adapter before proceeding to other polling strategies.

As we know, DB view restricts the update if it’s based on multiple tables so we would not be able to use ‘Logical Delete’ polling strategy in it’s original fashion. To overcome this, one of the common techniques is that use ‘Delete’ polling strategy and override the default delete query with update. This technique is well known among BPEL 10g developers.

For this post also, our use case remains same where we poll using DB view EMP_DETAILS_VIEW instead of table and inserts only active employees (ISACTIVE flag is Y).

DB View Definition:

SELECT e.employee_id, e.job_id, e.manager_id,
    e.department_id, e.isactive, d.location_id,
    l.country_id, e.first_name, e.last_name,
    e.salary, e.commission_pct, d.department_name,
    j.job_title, l.city, l.state_province,
    c.country_name, r.region_name
  FROM employees e, departments d,
    jobs j, locations l,
    countries c, regions r
  WHERE e.department_id = d.department_id
  AND d.location_id     = l.location_id
  AND l.country_id      = c.country_id
  AND c.region_id       = r.region_id
  AND j.job_id          = e.job_id;

Re-create DB adapter related files for polling using this DB view in JDeveloper and import them into OSB project as shown below.

After importing into OSB project, open the mappings.xml file and override the delete query to perform the update as shown below.

Create new proxy service from JCA file and add message flow similar to previous scenarios to call business service that inserts data into target table.

Deploy the jar file to OSB server and disable other proxy services except the recent one that we created, so that only one proxy service will be populating our target table. Update the ISACTIVE flag to Y in EMPLOYEES table.

Before polling:

After polling:

From the above screenshots, we can observe that ISACTIVE flag is set to N after polling the records from the DB view and also populated our target table as expected. The updated jar file can be found in the same location as given in previous posts.