SOA 12c - DB Adapter (Poll for New or Changed Records in a Table)

Here we go!
Information that you need to know:

Polling Strategies
The inbound receive enables you to listen to and detect events and changes in the database, which in turn can be the initiators of a business process. This is not a one-time action, but rather an activation. A polling thread is started, which polls a database table for new rows or events.

Whenever a new row is inserted into the MOVIES table, the polling operation raises it to the SCA Run Time. The strategy is to poll every record once. The initial SELECT has to be repeated over time, to receive the rows that exist at the start and all new rows as they are inserted over time. However, a new row once read is not likely to be deleted, and therefore can possibly be read repeatedly with each polling.

The various ways to poll for events, called polling strategies, also known as after-read strategies or publish strategies, range from simple and intrusive to sophisticated and nonintrusive. Each strategy employs a different solution for the problem of what to do after reading a row or event so as not to pick it up again in the next polling interval. The simplest (and most intrusive) solution is to delete the row so that you do not query it again. For more detailed information read the following URL: https://docs.oracle.com/cd/E28280_01/integration.1111/e10231/adptr_db.htm#CHDEFACG

In this post, we will learn how create a SOA Project with Database Adapter - Operation Type: Poll for New or Changed Records in a Table

The next images are the SOA Project execution: 

We are going to open JDeveloper IDE and click on File menu > New > Application.

For example:

On the Application window, set the next values, and click on next button.

Application Name	DatabaseAdapter
Directory	C:\JDeveloper\mywork\DatabaseAdapter
Application Package Prefix

For example:

On the Project window, set the next values, and click on next button.

Project Name	PollChangedRecordsTable
Directory	C:\JDeveloper\mywork\DatabaseAdapter\PollChangedRecordsTable

For example:

On the Configure SOA settings window, set the next values, and click on finish button.

Standard Composite

Empty Composite

For example:

After clicking on finish button, the JDeveloper IDE has the next appearance: 

Then the next step, on Exponsed Services section right click > Insert > Database, then enter the dbPollChangedRecordsTable value on Service Name, then click on next button.

For Example:

Select the Oracle JDBC connection, then click on next.

Note: If you need additional information about how create a Oracle JDBC connection click on the next URL: Create Oracle DB Connection

For Example:

Then on Operation Type window, chose the Poll for New or Changed Records in a Table option, then click on next button. 

Then on Select Table window, chose the table that you created, in my case I choose the XXISC_FGG_EMPLOYEES table >, then click on ok button, then next button.

For Example:

Then on Define Primary Keys window, select your primary key, in my case my primary key is EMPLOYEE_ID column, we keep the same values settings by default, then click on next button.

For Example:

Then on Relationships window, we keep the same values settings by default, then click on next button.

For Example:

Then on Attribute Filtering window, we keep the same values settings by default, then click on next button.

For Example:

Then on After Read window, we have five options, depends of your necessity you should be select the operation; in this case I choose Delete the Records(s), then click on next button.

For Example:

Then on Polling Options window, we keep the same values recommended settings by default, then click on next button.

For Example:

Then on Define Selection Criteria window, we keep the same values settings by default, then click on next button.

For Example:

Then on Advanced Options window, we keep the same values settings by default, then click on next button.

For Example:

Then on JCA Endpoint Properties window, we keep the same values settings by default, then click on next button.

For Example:

Then on Finish window, click on finish button.

For Example:

Then the next step, we need to select the BPEL Process component, this component is located on the right side, on Components section, when you found this, select it and drag it to Components section on work area.

For Example:

On the Create BPEL Process window, set the next values, and click on ok button.

BPEL Specification	BPEL 2.0 Specification
Name	BPELPollChangedRecordsTable
Namespace	http://xmlns.oracle.com/DatabaseAdapter/PollChangedRecordsTable/BPELPollChangedRecordsTable
Directory	C:\JDeveloper\mywork\DatabaseAdapter\PollChangedRecordsTable\SOA\BPEL
Template Type	WebService
Template	One Way BPEL Process
Service Name	bpelpollchangedrecordstable_client
Expose as a SOAP Service	Checked
Delivery	async.persist
Transaction
Input	{http://xmlns.oracle.com/DatabaseAdapter/PollChangedRecordsTable/BPELPollChangedRecordsTable}process

For example:

Now we need drag the database adapter service to BPEL process.

For example:

Now we are going to do double click on component: BPELPollChangedRecordsTable, then on BPELPollChangedRecordsTable.bpel file, we need delete the current Partnerlink Connection and create a new one between dbPollChangedRecordsTable and receiveInput 

Now in Edit Receive window, we need update the variable type, by defaul the inputVariable is created using the message type of the BPEL process, but for this exercise we need to change the type of variable and select a valide one, we need select the message type of the Database Adapter Service (WSDL).

Click on Browse Variables

For example:

 

On the Variable Chooser window, select the unique node and click on Edit The Selected Node option.

For example:

 

On the Edit Variable window, click on Browse and select the message type of the Database Adapter Service (WSDL).

For example:

Save all selected settings at this time.

For example:

We need to select the Assign activity and drag to work area, just in middle of receiveInput and End of the Control Flow and change the name from Assign1 to AssignEmployee

For example:

Then we need edit assign activity and create three variables.

For example:

The variables will serve to contain the information of the incoming record, the data types are of the following table:

employeeId	int
employeeFirstName	string
employeeLastName	string

Then click on Apply button, next click on Ok button,then click on Save All button.
For example:

Now we need to double click on AssignEmployee activity.

On the Edit Assign window, set the next values, and click on Ok button.

$inputVariable.XxiscFggEmployeesCollection/ns2:XxiscFggEmployees/ns2:employeeId	$employeeId
$inputVariable.XxiscFggEmployeesCollection/ns2:XxiscFggEmployees/ns2:firstName	$employeeFirstName
$inputVariable.XxiscFggEmployeesCollection/ns2:XxiscFggEmployees/ns2:lastName	$employeeLastName

Note: The values are an example, It's depends of your custom table

Then click on Apply button, next click on Ok button,then click on Save All button. 

For example:

Now we need to delete the Partner Link created by the BPEL process, click on yes button then click on Save All.

For example:

The next step is remove the Exposed Service created automatically by the BPEL component: 

* bpelpollchangedrecordstable_client_ep

Click on Yes button, Yes again and then click on Save All.

Now the next step is create a jar file to can deploy it on WebLogic Server, we need to select the PollChangedRecordsTable project and right click, then chose the PollChangedRecordsTable... option.

On the Deployment Action window, set the next values, and click on next button.

Deployment Action

Generate SAR File

For example:

On the Deployment Configuration window, set the next values, and click on next button.

New Revision ID

1.0

For example:

On the Deployment Summary window, click on Finish button.
For example:

If all steps were successful, then you will view on yours JDeveloper IDE the correctly deployment.
For example: