Adapter | My Tech Blog

Oracle 11G Database Adapter as Poller with BPEL process

December 16, 2012 sabapathy Leave a comment

Oracle Database Adapter works with Oracle BPEL Process and Oracle Mediator.
This Database adapter enable the BPEL and mediator to communicate with Oracle Database or other third party databases with JDBC.

In this post will see how to use the database adapter as a poller with BPEL process.
In this example one external service inserts records into database table. This Database adapter polls the records from the table and push it to BPEL process.
There is a column “Status” in the table. Initially it will be inserted with status “Ready”.
Once it is picked it will be updated to status “Processing”.
It will be updated to status “Completed” once the record has been successfully processed.

table structure and sample values

--CREATE TABLE 
CREATE TABLE SUB_CONNECT(
                         IDENTIFIER VARCHAR2(50) PRIMARY KEY,
                         REQUEST_XML CLOB NOT NULL,
                         CREATED_AT TIMESTAMP NOT NULL,
                         UPDATED_AT TIMESTAMP NOT NULL,
                         STATUS VARCHAR2(20) NOT NULL
                         );

--CREATE SEQUENCE TO CREATE UNIQUE IDENTIFIER COLUMN
CREATE SEQUENCE SB_CONN_SEQ START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;

-- INSERT SAMPLE VALUES
INSERT INTO SUB_CONNECT VALUES('IDENTIFIER'||SB_CONN_SEQ.NEXTVAL,'<REQUEST><NAME>SABA1</NAME><CITY>SYDNEY</CITY></REQUEST>',SYSDATE,SYSDATE,'Ready');
INSERT INTO SUB_CONNECT VALUES('IDENTIFIER'||SB_CONN_SEQ.NEXTVAL,'<REQUEST><NAME>SABA2</NAME><CITY>SYDNEY</CITY></REQUEST>',SYSDATE,SYSDATE,'Ready');
INSERT INTO SUB_CONNECT VALUES('IDENTIFIER'||SB_CONN_SEQ.NEXTVAL,'<REQUEST><NAME>SABA3</NAME><CITY>SYDNEY</CITY></REQUEST>',SYSDATE,SYSDATE,'Ready');
INSERT INTO SUB_CONNECT VALUES('IDENTIFIER'||SB_CONN_SEQ.NEXTVAL,'<REQUEST><NAME>SABA4</NAME><CITY>SYDNEY</CITY></REQUEST>',SYSDATE,SYSDATE,'Ready');
INSERT INTO SUB_CONNECT VALUES('IDENTIFIER'||SB_CONN_SEQ.NEXTVAL,'<REQUEST><NAME>SABA5</NAME><CITY>SYDNEY</CITY></REQUEST>',SYSDATE,SYSDATE,'Ready');
INSERT INTO SUB_CONNECT VALUES('IDENTIFIER'||SB_CONN_SEQ.NEXTVAL,'<REQUEST><NAME>SABA6</NAME><CITY>SYDNEY</CITY></REQUEST>',SYSDATE,SYSDATE,'Ready');
INSERT INTO SUB_CONNECT VALUES('IDENTIFIER'||SB_CONN_SEQ.NEXTVAL,'<REQUEST><NAME>SABA7</NAME><CITY>SYDNEY</CITY></REQUEST>',SYSDATE,SYSDATE,'Ready');
INSERT INTO SUB_CONNECT VALUES('IDENTIFIER'||SB_CONN_SEQ.NEXTVAL,'<REQUEST><NAME>SABA8</NAME><CITY>SYDNEY</CITY></REQUEST>',SYSDATE,SYSDATE,'Ready');
INSERT INTO SUB_CONNECT VALUES('IDENTIFIER'||SB_CONN_SEQ.NEXTVAL,'<REQUEST><NAME>SABA9</NAME><CITY>SYDNEY</CITY></REQUEST>',SYSDATE,SYSDATE,'Ready');
INSERT INTO SUB_CONNECT VALUES('IDENTIFIER'||SB_CONN_SEQ.NEXTVAL,'<REQUEST><NAME>SABA10</NAME><CITY>SYDNEY</CITY></REQUEST>',SYSDATE,SYSDATE,'Ready');
INSERT INTO SUB_CONNECT VALUES('IDENTIFIER'||SB_CONN_SEQ.NEXTVAL,'<REQUEST><NAME>SABA11</NAME><CITY>SYDNEY</CITY></REQUEST>',SYSDATE,SYSDATE,'Ready');
INSERT INTO SUB_CONNECT VALUES('IDENTIFIER'||SB_CONN_SEQ.NEXTVAL,'<REQUEST><NAME>SABA12</NAME><CITY>SYDNEY</CITY></REQUEST>',SYSDATE,SYSDATE,'Ready');
INSERT INTO SUB_CONNECT VALUES('IDENTIFIER'||SB_CONN_SEQ.NEXTVAL,'<REQUEST><NAME>SABA13</NAME><CITY>SYDNEY</CITY></REQUEST>',SYSDATE,SYSDATE,'Ready');
INSERT INTO SUB_CONNECT VALUES('IDENTIFIER'||SB_CONN_SEQ.NEXTVAL,'<REQUEST><NAME>SABA14</NAME><CITY>SYDNEY</CITY></REQUEST>',SYSDATE,SYSDATE,'Ready');
INSERT INTO SUB_CONNECT VALUES('IDENTIFIER'||SB_CONN_SEQ.NEXTVAL,'<REQUEST><NAME>SABA15</NAME><CITY>SYDNEY</CITY></REQUEST>',SYSDATE,SYSDATE,'Ready');
INSERT INTO SUB_CONNECT VALUES('IDENTIFIER'||SB_CONN_SEQ.NEXTVAL,'<REQUEST><NAME>SABA16</NAME><CITY>SYDNEY</CITY></REQUEST>',SYSDATE,SYSDATE,'Ready');
INSERT INTO SUB_CONNECT VALUES('IDENTIFIER'||SB_CONN_SEQ.NEXTVAL,'<REQUEST><NAME>SABA17</NAME><CITY>SYDNEY</CITY></REQUEST>',SYSDATE,SYSDATE,'Ready');

--SELECT TABLE SUB_CONNECT
SELECT * FROM SUB_CONNECT;

Create DB Adapter to poll record with status “Ready”
1.Create SOA Application
2.Create SOA Project with Empty Composite.

EmptyComposite

3. Create the Database Adapter
3.1. Right click on Exposed Services left side panel and select Database Adapter. It will prompt the Database Adapter creation window.

SelectDatabaseAdapter

DBConfigWizad

DBAdapter3

3.2. Create Database Connection

CreateDBConnection

CreateDBConnJNDI

3.3. Select Operation Type

The Database Adapter will be processed by two threads.
1. One thread will poll the record from the table and push it to BPEL process “receiveInput” point.
2. Next thread will carry out the BPEL Process.

This entire Database Adapter and BPEL Process will be handled by single thread if Do Synchronous Post to BPEL(Allows In-Order Delivery) option has been selected.

OperationType

3.4. Then Import table where the poller should point and Click Next.

ImportTable

DBAdapter8

Relationships

Attributes

3.5. Select After Read polling strategies.

Update a Field in the SUB_CONNECT table (Logical Delete). This will read the Data from the table with status value is “Ready”.

AfterReadPollingStrategy

Logical Delete Field : Column Name on Table , based on this column value (“Ready”) the poller will pick the record.
Read Value : The value to indicate that the row has been read . (“Processing”)
Unread Value : (Optional) Enter an explicit value to indicate that the row does not need to be read. During polling, this row is skipped. . (“Completed”)
Reserved Value:
Reserved Value : This value is to indicate that the row is reserved. Blocked

LogicalDelete

3.6. Polling Options.

Polling Frequency: Select how frequently to poll for new records or events.
Database Rows per Transaction: Number of record has to be polled per bpel instance.
Order By: Poller will picks the records based on the “Updated_At” time value.
Distributed Polling: This option has to be enabled when this Database Adapter is deployed in multiple soa managed servers.

PollingOptions

SelectionCriteria

3.7. Retry Options.

Default retry option is available in Database Adapter.Use to specify the value for auto-retry incase of timeout. In case of a connection related fault the invoke can be automatically retried a limited number of times.
Example:

Attempts
Specify the number of times to acquire a new SQL connection and retry the invoke.

Interval (s)
Specify the time interval between each retry.

Backoff Factor: x
Allows you to wait for increasing periods of time between retries. 9 attempts with a starting interval of 1 and a back off of 2 will lead to retries after 1, 2, 4, 8, 16, 32, 64, 128, and 256 (28) seconds.

Max Interval (s):
Specify the maximum interval for retries.