Polling database table using Oracle BPEL

The BPEL DB adapter has an inbuilt feature called ‘Poll for database events‘. We can use this feature to kick start a BPEL process whenever a record gets inserted into a specific database table. For example, The BPEL – DB Adapter checks a specific table for a specific interval (Ex: 5sec) and finds if any new row gets added to that table with a filter we specified (ex: Where name like). If it finds a record then BPEL starts the process else it will wait another 5sec, monitors the table again and the process continues. It is an infinite loop, runs as long as the composite runs on the WebLogic server.

In this section, we created a BPEL process with DB_Polling adapter that monitors an employee table in HR schema and checks for a new record. If it finds a record, then it will send an email to the employee.

Note: This BPEL process will delete the record after reading it, so backup your table before deploying the process to the server

 

Pre-Requisite

JDeveloper 12C

SOA Application (Click here for instruction to create an SOA application)

HR Schema (Comes with Oracle database)

 

BPEL DB Polling Adapter Example: 

  1. Create an SOA Composite application 

 

     2. Open the composite and delete any existing element like below 

 

051916_0332_Monitortabl1 Oracle BPEL - Database Adapter polling

 

3. On the empty composite >> Right click on the exposed services >> Select Insert >> Click Database

 

051916_0332_Monitortabl2 Oracle BPEL - Database Adapter polling

 

4. The DB adapter wizard opens on the screen >> Input name as ‘EmployeeMonitor’ >> Click next

 

051916_0332_Monitortabl3 Oracle BPEL - Database Adapter polling

 

5. In the Service Connection wizard >> Click + to add a database connection

 

051916_0332_Monitortabl4 Oracle BPEL - Database Adapter polling

 

6. Input HE schema connection >> Test the connection >> Click Ok

 

051916_0332_Monitortabl5 Oracle BPEL - Database Adapter polling

 

7. The JNDI name populates automatically in the wizard. You modify the JNDI name as your convention or add an existing one by click the Search icon, then find it from the WebLogic server if configured already >> Click Next

Note: The JNDI name is important. When you deploy the BPEL code in WebLogic server, the process expects the JNDI name in the WebLogic. If the process not finds, then it throws JCA adapter error and deployment fails. In my other blog, I have explained how to add a JNDI name in DBAdapter in WebLogic server.

 

051916_0332_Monitortabl6 Oracle BPEL - Database Adapter polling

 

8. Select the option ‘Poll for New or Changed Records in a table’ >> then Click Next

 

051916_0332_Monitortabl7 Oracle BPEL - Database Adapter polling

 

9. Click the button Import Tables to add an existing table

 

051916_0332_Monitortabl8 Oracle BPEL - Database Adapter polling

 

10. Point the HR schema >> Click Query >> Move the Employees Table to the right >> Click Ok

 

051916_0332_Monitortabl9 Oracle BPEL - Database Adapter polling

 

11. The Employee record populated in the wizard >> Click Next

 

051916_0332_Monitortabl10 Oracle BPEL - Database Adapter polling

12. Click Next

 

051916_0332_Monitortabl11 Oracle BPEL - Database Adapter polling

 

13. Choose the fields you required >> Click Next

 

051916_0332_Monitortabl12 Oracle BPEL - Database Adapter polling

 

14. Choose the option ‘Delete the Rows that were Read’ >> Click Next.

 

051916_0332_Monitortabl13 Oracle BPEL - Database Adapter polling

 

15. Enable the Distributed Polling >> Accept the remaining >> Click Next

 

051916_0332_Monitortabl14 Oracle BPEL - Database Adapter polling

 

16. Using the Edit button to add any filter by column or click Next

 

051916_0332_Monitortabl15 Oracle BPEL - Database Adapter polling

 

17. Click Next

 

051916_0332_Monitortabl16 Oracle BPEL - Database Adapter polling

 

18. Increase the interval to 4 and Click Next

 

051916_0332_Monitortabl17 Oracle BPEL - Database Adapter polling

 

19. Click Finish to close the wizard

 

051916_0332_Monitortabl18 Oracle BPEL - Database Adapter polling

 

20. The EmployeeMonitor will get created in the Exposed Service

 

051916_0332_Monitortabl19 Oracle BPEL - Database Adapter polling

 

Add a BPEL Process

 

21. Right click on the Components >> Select Insert >> Click BPEL process

 

051916_0332_Monitortabl20 Oracle BPEL - Database Adapter polling

 

22. Choose as shown below in the wizard >> Click Next

 

051916_0332_Monitortabl21 Oracle BPEL - Database Adapter polling

 

23. Connect the EmployeeMonitor and BPEL Process as shown below

 

051916_0332_Monitortabl22 Oracle BPEL - Database Adapter polling

 

24. Open the BPEL Process >> Delete the connection between BpelProcess2 and recieiveInput >> Establish the connetion between EmployeeMonitor and receiveInput

 

051916_0332_Monitortabl23 Oracle BPEL - Database Adapter polling

 

25. Edit Receive appears on the screen >> Click the Search Icon

 

051916_0332_Monitortabl24 Oracle BPEL - Database Adapter polling

 

26. Select the InputVariable >> Click the Edit icon

 

051916_0332_Monitortabl25 Oracle BPEL - Database Adapter polling

 

27. Click the Search Icon

 

051916_0332_Monitortabl26 Oracle BPEL - Database Adapter polling

 

28. Expand the Message Types >> Expand the MessageTypes >> Select the EmployeesColection_msg >> Click Ok >> Ok >>Ok >> Ok

 

051916_0332_Monitortabl27 Oracle BPEL - Database Adapter polling

 

29. Delete the bpelProcess

 

051916_0332_Monitortabl28 Oracle BPEL - Database Adapter polling

 

30. The final process looks like below

 

051916_0332_Monitortabl29 Oracle BPEL - Database Adapter polling

 

31. Search email in components >> Drag Email and drop it below receiveInput as shown below

 

051916_0332_Monitortabl30 Oracle BPEL - Database Adapter polling

 

32. Double click on the Email component

 

051916_0332_Monitortabl31 Oracle BPEL - Database Adapter polling

 

33. Click the Expression Editor in the Edit Email

 

051916_0332_Monitortabl32 Oracle BPEL - Database Adapter polling

 

34. Expand the input variable >> Drag the email to the expression as shown below > Click Ok

 

 

051916_0332_Monitortabl33 Oracle BPEL - Database Adapter polling

 

35. Click the expression editor near by Body

 

051916_0332_Monitortabl34 Oracle BPEL - Database Adapter polling

 

36. Move the first name to the expression >> Click Ok

 

051916_0332_Monitortabl35 Oracle BPEL - Database Adapter polling

 

37. Final BPEL looks like below

 

051916_0332_Monitortabl36 Oracle BPEL - Database Adapter polling

 

38. Go to the composite >> Delete the bpelprocess_client>> Connet the EmployeeMonitor and BpelProcess. The final composite will look like below

 

051916_0332_Monitortabl37 Oracle BPEL - Database Adapter polling

govindan Oracle BPEL - Database Adapter polling
Connect me

Govind

Thank you for visiting my personal blog. Myself Govindan, Software Developer by profession since 2006 and hence I started this blog early in 2016 and ever since I've been writing about technologies experienced and learnings of everyday life.

The views expressed on this blog are my personal views and do not necessarily reflect the views of my employer.

Please feeling free to reach me on any comments and feedbacks you have. Would be more than glad to listen and reply šŸ™‚
govindan Oracle BPEL - Database Adapter polling
Connect me
By | 2017-03-24T21:32:02+00:00 May 18th, 2016|Categories: BPEL|Tags: , |2 Comments
Like us on Facebook.
Connect!