AVIO Consulting

Part 4 – Oracle BPM 12c Process Invoke Database

May 2, 2017 | Oracle

This is the 4th 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.

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 4 – Create a SOA Composite Project to Invoke the Database Adapter through a Mediator

This explains how to create the SOA Composite Project that invokes the database schema defined in the Database Adapter in parts 1 – 3 of this series.

In part 5 of this series, the service exposed in this part will be invoked by the BPM Composite Project’s process.

Open JDeveloper and click File -> New -> Project -> as shown below, select SOA Project on the right.

Create SOA Project

Click OK. Name the service HRService.

Name the project

Click Next. We are going to manually add the Mediator so click Empty Composite (normally you would simply click Composite with Mediator here).

Create empty composite

Click Finish.

Add a Database Adapter to the Composite

The Composite Editor should now be open (if it does not open or to reopen it again later, in Applications tab double click -> HRService that represents this project’s composite.xml file in the -> SOA folder). From the Component Palette on the right, select Database and drag it to the External References swim lane in the Composite Editor as shown below.

Add a Database Adapter

This automatically launches the Database Adapter Configuration Wizard. Click Next -> name the new Database Adapter the service HR_Employee -> Next.

Name the adapter

In the Service Connection dialog, define a new connection by clicking the -> Add button.

In the Create Database Connection dialog, enter HR in the Connection Name field -> in the Username field enter HR-> enter the schema’s password in the Password field -> enter localhost in the Host Name field -> ensure 1521 is entered in the JDBC Port field -> enter XE (if you are using Oracle XE) or ORCL (if you are using Oracle SE) in the SID field.

Configure the database schema's connection

Double check that these fields are entered exactly as shown above and then click the Test Connection button to verify that the entries are correct.

Test the connection

Click OK.

This returns you back to the Service Connection dialog. Set the JNDI Name field to eis/DB/HR. This is the name that has already been configured for the Database Adapter for this database on the Administration Console in parts 2 and 3 of this series.

Enter the JNDI connection string

Click Next.

Select a Table for the HR_Employee Adapter

The different types of operations you will incorporate into your service are selected in the Operation Type dialog shown below. In this example, you will use the Database Adapter to do a select on the HR database schema.

From this dialog select the Perform an Operation on a Table option -> leave only the Select option checked.

Pick the SELECT option

Click Next.

The next series of steps are to select the Employee table that we will be using. Click the Import Tables button in the lower left corner -> click the Query button -> select the EMPLOYEES table from the list on the left and click the > button to add it to the Selected list on the right.

EMPLOYEES table selected

Click OK.

Wait a few seconds and you should see the Select Table dialog with EMPLOYEES now in the list.

Employees selected

Click the Next button.

Create SQL in HR_Employee Adapter

Here you will see how SQL can be in JDeveloper.

Because the EMPLOYEES table is related to other tables in the HR schema, you are given an opportunity name the relationships to the other tables in the Relationships dialog. There is nothing to do here so click the Next button.

EMPLOYEES table relationships

In the Attribute Filtering dialog you select the columns that will be included in the SQL SELECT statement. As shown below, to limit the columns returned uncheck the checkboxes beside jobId and commisionPct.

Select the columns to be returned

Click Next.

The Define Selection Criteria dialog is a little confusing, but here’s how you get it started. Beside the Parameters section click the Add button.

Add a parameter

Enter the parameter employeeId.  This parameter will be used in the select statement to identify the one row to return from the EMPLOYEES table.

Enter parameter name

Click OK -> click the Edit button that is beside the SQL section.

Edit query with parameter

This opens the SQL Expression Builder shown below.

Add the parameter

Click the Add button -> change the Second Argument’s radio button to Parameter which automatically selects the employeeId parameter you just added.

Select based on parameter

Click OK. This completes the SQL Expression Builder wizard.

Verify that the SQL was built as shown below before continuing.

Parameter query completed

Click Next.

In the Advanced Options dialog, because we are using the unique primary key EMPLOYEE_ID as our selection criteria change the Max Rows field to 1.

Max Rows 1

Click Next -> Next -> click Finish to complete the configuration of the new HR_Employee Database Adapter service.  Note the new HR_Employee database adapter in the External References column.

Database Adapter Added to External References

Add a Mediator Component in the Composite Editor

Drag the Mediator component from the Service Components section of the Component Palette into the Components swim lane in the Composite Editor.

Add Mediator to the Composite

Name the Mediator HRServiceMediator, leave the dropdown set to Define Interface Later selected -> click OK to create the Mediator in the composite.

Mediator Added

Add SOAP Web Service Binding

The next few steps exposes the composite application as a SOAP web service.

Drag SOAP from the Component Palette to the Exposed Services swim lane.

Expose Composite as a SOAP service

In the Create Web Service wizard dialog, enter GetEmployeeById in the Name field -> select the Generate WSDL from Schema button (the button is to the right of the WSDL URL field).

Generate WSDL

From the Interface Type dropdown, select Synchronous Interface.

In the Input section, click the + icon

Add an input element

Change the Part Name to employeeId -> click OK.  By doing this, a string that contains the employee id will be used as the input.

input string

Download the XSD that will be used for the output in the next step from here.

In the Output section, click the + icon -> change the Part Name to employee -> click the Browse icon -> click the Import Schema File icon (in the upper right corner) -> click the Location dropdown -> select the HREmp_forSoa.xsd file you just downloaded -> click OK -> OK -> select the -> EmployeeInfo element.

Select the Type for output

Click OK ->  OK

Verify that your input and output mapping matches what is shown below.

input and ouput for SOAP service

Click OK ->  click the copy wsdl and its dependent artifacts into the project checkbox.

Click OK.


Connect the Wiring in the Composite Editor

The Composite model now has the three disconnected components shown below. In this task, you will connect them together.

Composite before connecting components

Start by selecting the >> icon on the right of the Web Service and extending it to the left side of the Mediator and then releasing it.

Connect the service to the Mediator

Now connect the Mediator to the Database Adapter by first selecting the Mediator -> select the triangle icon on the right side of the Mediator and drag it to the Database Adapter’s >> icon.


Define the Mediator Transformations

The routing a Mediator can perform is determined by the wire connections you just made to the Web Service and Database Adapter components. In this case, it is a one-to-one wiring to the DB Adapter so the Mediator’s routing is relatively simple. The Mediator’s responsibility in this composite is to marshal and transform the data passed between the Web Service input request and the SOA components.

Double click the HRServicesMediator. Because this is a synchronous operation, there is an input and output transformation that must be completed.  You will do this in the next few steps

Mediator Mapping

Starting with the input transformation, because it is a simple mapping click the Assign Values button.

Assign Values button

Fully + expand the in and the out -> map the two employeeId elements.

Map employeeId elements

Click OK.

Now map the data flowing back from the database that feeds the output of the web service. Click the Transform Using  transformation icon for the Synchronous Reply.

Transform mapping

Click + to create a new XSLT transformation mapper file.

Create the XSLT Transformation mapper file

Click OK -> OK -> OK.

Expand the Source’s Employees collection element.

Expand the Employees collection

As shown below, map the source’s  Employees to the target’s  EmployeeInfo to automatically map very similarly named elements to one another.

Map Employees to EmployeeInfo

+ Expand the if elements on the right and note that except for the phoneNumber, all of the elements that were automatically mapped. 

Expand the if elements

Before mapping phoneNumber to ContactPhone change JDeveloper’s XLST mapping preference to automatically add an if  when mapping optional elements.  From JDeveloper’s menu, click Tools -> Preferences -> +expand XSL Maps -> select XSL Editor -> select the checkbox Map source node, insert xsl:if checking source node existence.

Map source node and insert if checking

Click OK.

Back in the XSLT mapper, map the phoneNumber to ContactPhone.

Map phone number

Note that after adding this, an if was added to check for the optional element’s existence before mapping the source phoneNumber element to the target ContactPhone element.

if automatically added in XSLT

Click Save.

Deploy the HRService to the SOA Application Server

Deploy this project to the SOA Server so the composite application can be tested.

In the Application Navigator right mouse click HRService -> Deploy -> HRService ->  Next ->  Next ->  select the BPM Application Server ->  Next ->  Next ->  Finish.


Test the GetEmployeeById Web Service Using Enterprise Manager

Open Enterprise Manager (e.g., https://www.avioconsulting.com/:7001/em) in your browser. Login using the weblogic credentials.

Expand the SOA folder -> default -> select the new HRService.

Select the Composite for testing the Database Adapter composite

Click the Test button.


Enter 100 in the employeeId field as shown below (there is a row on the Employees table that has 100 as the id).

Enter valid employee id

Scroll up and click the Test Web Service button in the upper right corner, and note the response that is returned from the database.

Successfully invoke service