Database Polling in OSB – Part III

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.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.


0 Responses to “Database Polling in OSB – Part III”

  1. Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.


Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 379 other subscribers

Enter your email address to follow this blog and receive notifications of new posts by email.

%d bloggers like this: