AVIO Consulting

Part 5 – Oracle BPM 12c Process Invoke Database

May 2, 2017 | Oracle

This is the 5th of a 5 part series that explains how to expose a database to an Oracle BPM 12c Process using the Oracle SOA Database Adapter and a Mediator.  In this, the Composite with the Database Adapter created in part 4 of this series is invoked by the Oracle BPM process using a Service activity.

Part 1 – Create a WebLogic JNDI Database Connection

Part 2 – Configure the Database Adapter’s Outbound Connection Pool

Part 3 – Configure the Database Adapter’s JNDI Connection to the Database

Part 4 – Create a SOA Composite Project to Invoke the Database Adapter through a Mediator

Part 5 – Invoke the Service Exposed from a Process in the BPM Composite Project

Part 5 – Invoke the Service Exposed from a Process in the BPM Composite Project

In this part, based on the id field that is entered – you will retrieve the salary and other information about a candidate stored in the database table fed into the BPM process. 

Download the BPM Project that will be used to invoke the service from here.  (This is a project)

Open the project and open the Candidate Approval process.

To open the BPM Project’s composite diagram, click the icon located in the top right corner of the process model diagram.

Candidate Approval process - open composite diagram

Drag the  SOAP Web Service component into the External References column in the Composite diagram.

Add SOAP Adapter into External References

Name the service HRService (1 below) -> click the Find existing WSDLs icon (2) ->  select Application Server (3) ->  click the dropdown and select the application server (BPM_Compact in this example) (4) ->  + expand the composites as shown below until you see the previously deployed HRService service ->  +expand the composite labeled HRService [Default…] and select the GetEmployeeById service (5).

Define the HRService External Reference

Click OK.

Check the copy wsdl and its dependent artifacts into the project checkbox.

Copy wsdl and its dependent artifacts r

Click OK -> OK  ->  Save.

Reopen the Candidate Approval process. As shown below, create a gap between the Message Start Event and the Script activity and add a Service activity in the sequence flow in this gap.

Add a Service Activity into the process


Name it Retrieve Candidate Information.

Name the Service Activity

Click the Implementation tab -> change the Type dropdown to Service Call ->  click the Browse icon ->  click HRService ->  OK (if you do not see HRService, stop and restart JDeveloper) ->  click the Data Associations link.

Select the Service and open the Data Associations dialog

With the Input tab selected, as shown below drag the employeeIdInput element on the left to employeeId on the right.

Match input arguments to the Service activity

Set the outgoing information by clicking the Output tab on the left -> + expand employeeInfo.

Output mapping


On the right, + expand employee -> + expand employeeSummary.

Expand the output

To set the value of the data retrieved from the database table drag the XSL Transformation icon over the employee element.

Add XSLT Mapping to employee output

Select employeeInfo from the list on the left -> click the > icon to move it to the right.

Select employeeInfo for the XSL transformation to employee

Click OK -> OK -> OK ->  Save All.

Drag EmployeeInfo on the left to EmployeeSummary (not EmployeeInfo) to the right.

Map EmployeeInfo to EmployeeSummary

Click OK when the automap dialog appears.  Your XSL transformation should now look like this.

XSL Transformation for the output

Save the project.

Deploy the EmployeeOnboarding project.

As you did previously in the 4th blog in this series, open Enterprise Manager and test the deployed composite.  This time, instead of testing the HRService composite, select the EmployeeOnboarding project that was just deployed.

Test the EmployeeOnboarding project

Expand the parameters -> enter 101 in the employeeId field -> click the Test Web Service button.

Enter an input to test the process

With the Response tab open click Launch Flow Trace.

Response tab

Select the CandidateApproval process.

Select the process in the flow trace

Select the dropdown and click Tree View.

Tree view Audit Trail

Expand the After Service Call activity -> click one of the links to the right.

Expand the Script activity

Note that the call to the service was successful and data was returned to the process.