Advertisements



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

dbview

PollStartegy

PollOptions

isactive

wherecondn

OSBImport

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

override

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.

msgflow

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:

beforeinview

beforetargettbl

After polling:

aftertblview

aftertargettbl

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.

Advertisements

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:

WordPress.com Logo

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

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s




Advertisements

Pages

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

Join 341 other followers

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