Archive
Oracle 11G Database Adapter as Poller with BPEL process
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.
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.
3.2. Create Database Connection
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.
3.4. Then Import table where the poller should point and Click Next.
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”.
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
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.
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.
4. Composite with Database Adapter
4.1Right Click on “Components” screen and select “BPEL Process”.
4.2 Name the BPEL component,namespace, select Template and select input ,output from XSD created while DB Adapter creation.