Expose a Database to an Oracle BPM 12c Process Using Oracle SOA Database Adapter

 

By Oracle ACE Dan Atwood

July 2017

This 4-part article 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

This section explains how the WebLogic JNDI connection to a database schema is created. Part 2 will explain how the Database Adapter can use this connection once it has been created.

Components exposed in a SOA application can be exposed as a service, and a call to the database is no exception. In order to invoke the database from a BPM process, it first has to be exposed as a service.

First, a data source needs to be created. The database schema's data source is then configured to include the JNDI connection information needed to access it. If all that is needed is to access the data from ADF, this is all that would be needed.

The database adapter comes into play, however, when a project's composite needs to access it as a service (e.g., when a composite's Mediator needs to connect to the database adapter). In the part 3 of this article, the Database Adapter SOA component will be dragged into the External References column of the SOA composite diagram of the project. For this to succeed at runtime, the datasource's JNDI connection to the datasource has to first be added to the database adapter's DBAdapter deployment. This will be done in part 2 of this article.

This example uses the HR schema that comes is preinstalled with the Oracle XE 11g and 12c SE databases, but these same steps could also be used to expose other database schemas.

  1. Open the WebLogic Console (e.g.,http://localhost:7001/console), and log in.
  2. In this step, the source of the data is configured as a data source to the SOA infrastructure. This data source provides the connection to the actual underlying data provider. Configure the JDBC data source in the WebLogic by selecting ServicesData Sources.

    atwood-bpm-fig01
    Figure 1: Data Sources

  3. Click New Generic Data source.
  4. JNDI stands for Java Naming and Directory Interface. This is a standard interface for the Java based application clients to access the underlying naming and directory services like DNS, LDAP, JMS and database schemas. This allows a client to access different services in a standard way regardless of the underlying technology of the target.

    Take note of the JNDI name being entered in this step. It will be something like "jdbc/myDBSourceName". This is a multi-step wizard, and the last step points to the correct server by clicking the appropriate checkbox in the list.

  5. Configure the JDBC data source to point to the HR schema that comes with Oracle 11g and 12c databases. Note the JNDI name of the JDBC data source: jdbc/hr. This same value is going to be used later to configure the Connection Pool in part 2 of this article. In this example, the name of the data source is set to HR (# 1 in Figure 2, below), the JNDI name is set to jdbc/hr (#2 below). Click Next (#3 below) to continue.

    atwood-bpm-fig03
    Figure 2: JDBC Datasource

  6. For Oracle XE or SE, set the driver to "Oracle's Driver (Thin XA) for instance connections...". Click Next.
  7. For an Oracle XE or SE development test environment, leave the "Supports Global Transactions" checkbox unchecked (a runtime error will occur at if it is checked). Click Next.
  8. For the HR schema installed on an Oracle XE database, enter XE as the Database Name, otherwise enter ORCL, then for a test development environment, enter localhost as the Host Name, set the Database User Name to HR, then set the Password and Confirm Password fields to the password for the HR schema, then click Next.
  9. Click the Test Configuration button and verify that it tested successfully.

    atwood-bpm-fig07
    Figure 3: Test the connection to the database schema

    Click Next.
  10. If a separate SOA managed server installed in the development test environment, select the checkbox beside the soa_server, then click Finish.

    atwood-bpm-fig08
    Figure 4: Select SOA as the target

    Having done this, the new data source should be in the list with these settings.

    atwood-bpm-fig09
    Figure 5: HR is present in the data source list

If the SOA Database Adapter was not going to be used to connect to the database, there would be no need to configure it. Because parts 3 and 4 of this article use this adapter to access customer information, part 2 shows how to add the JNDIconnection information that was created here to the Database Adapter's configuration.

Part 2 - Configure the Database Adapter

Configure the Outbound Connection Pool

In the first part of this article, the JNDI connection to the database schema was configured. Because the Database Adapter will be used to access the information from the database, it needs to become aware that the schema exists. Here the JNDI connection information will be added to the deployed Database Adapter.

  1. To configure the Database Adapter to use the jdbc/hr JNDI name created in part 1 of this article, click Deployments, then click DBAdapter.

    atwood-bpm-fig010
    Figure 6: Database Adapter Deployment

  2. Next, the connection pool will be configured. WebLogic communicates with the database through a connection pool. Rather than incurring the overhead of constantly creating and disposing of connections, this pool allows WebLogic to use a fixed number of connections to databases Each data source has a connection pool assigned to it, which it uses for connecting to the actual data provider (the database schema in this case). Recall that when creating the data source, a JNDI name was provided. This same JNDI name will be used when configuring the connection pool. Click the Configuration tab (1 below), click the Outbound Connection Pools tab (2), expand javax.resource.... (3), then click New (4).

     atwood-bpm-fig011

    Figure 7: Outbound Connection Pool

  3. Click the radio button beside the connection factory, then click Next.
  4. In the JNDI Name field, enter the JNDI name to be used when creating the database connection in JDeveloper in Part 3 of this article: eis/DB/HR (this is case sensitive). Then click Finish.

     Click OK. You should see the following:

    atwood-bpm-fig014
    Figure 8: Outbound Connection Complete

Configure the Database Adapter's JNDI Connection to the Database

  1. With the Database Adapter's "Outbound Connection Pools" tab still selected, expand javax.resource.cci.ConnectionFactory once again, then click the new eis/DB/HR connection that was just created.
  2. Click in the "xADataSourceName" field, then enter the same text that was entered when the data source to the underlying database schema was originally created and configured in Part 1 (jdbc/hr in this example).

     atwood-bpm-fig016

    Figure 9: xADataSourceName Field Database Name

     Be careful here. There is a trick to getting this text to be saved. After entering the text, be sure to hit the Enter key. If this is not done, the text that was entered will not be saved in the xADataSourceName field.

  3. Click the Save button. Double check to ensure that the xADataSourceName field has stored the correct value, and notice the message at the top with the reminder to update the deployment of the database adapter. This is necessary to have the changes that were just made to take effect. To update the deployment, click Deployments (1 below), then click the checkbox beside DbAdapter (2), then click the Update button (3).

     atwood-bpm-fig019

    Figure 10: Update the Deployment

    Click Next (assuming the deployment profile does not need to be changed), then click Finish.

    The JNDI connection should now be ready to use and the Database Adapter is ready to access the HR database schema. In part 3 of this article this adapter will be added to a SOA Composite and exposed through a Mediator.

Part 3 - Create a SOA Composite Project to Invoke the Database Adapter through a Mediator

This explains how to create the SOA Composite Project with a Database Adapter that uses the database JNDI defined in part 1 of this article. In the next and final part of this article, the service exposed here will be invoked by the BPM Composite Project's process.

  1. Open JDeveloper and click FileNew Projects → as shown below, then select SOA Project on the right.

    atwood-bpm-fig021
    Figure 11: Create SOA Project

    Click OK.
  2. Name the service HRService, then click Next. The Mediator is going to be manually added so click Empty Composite (normally simply click Composite with Mediator here) , then click Finish.

Add a Database Adapter to the Composite

  1. The Composite Editor should now be open (if it does not open or it needs to be reopened, in Applications tab double click the 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 column in the Composite Editor, as shown below.

     atwood-bpm-fig024

    Figure 12: Add a Database Adapter

    This automatically launches the Database Adapter Configuration Wizard. Click Next, name the new Database Adapter HR_Employee, then click Next.
  2. In the Service Connection dialog, define a new connection by clicking the → Add button.
  3. 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 (for Oracle XE) or ORCL (for Oracle SE) in the SID field.

     atwood-bpm-fig026

    Figure 13: Configure the database schema's connection

  4. 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. Click OK. This is the name that has already been configured for the Database Adapter for this database schema in the Administration Console in part 2 of this article.

     atwood-bpm-fig028

    Figure 14: Enter the JNDI connection string

    Click Next.

Select a Table for the HR_Employee Adapter

  1. The different types of operations that will be incorporated into the service are selected in the Operation Type dialog shown below. In this example, the Database Adapter will do a SELECT on the HR database schema so from this dialog select the Perform an Operation on a Table option → leave only the Select option checked.

     Click Next.

  2. The next series of steps are to select the Employee table that will be read. 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.

     atwood-bpm-fig030

    Figure 15: EMPLOYEES table selected

    Click OK. Wait a few seconds and the Select Table dialog with EMPLOYEES will appear in the list. Click the Next button.

Create SQL the EMPLOYEES Table from the Adapter

  1. Because the EMPLOYEES table is related to other tables in the HR schema, the foreign keys to the other tables is shown in the Relationships dialog. There is nothing to do here so click the Next button.
  2. In the Attribute Filtering dialog, 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.

     atwood-bpm-fig032

    Figure 16: Select the columns to be returned

    Click Next.

  3. Adding parameters to refine the SELECT statement in the Define Selection Criteria dialog is a little confusing. Beside the Parameters section click the Add button.

     atwood-bpm-fig033

    Figure 17: 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. Click OK → click the Edit button that is beside the SQL section. This opens the SQL Expression Builder. Click the Add button → change the Second Argument's radio button to Parameter which automatically selects the employeeId parameter that was just added.

    atwood-bpm-fig037
    Figure 18: Select based on parameter

    Click OK. This completes the SQL Expression Builder wizard.

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

     atwood-bpm-fig038

    Figure 19: Parameter query completed

  5. Click Next. In the Advanced Options dialog, because the unique primary key EMPLOYEE_ID as the selection criteria change the Max Rows field to 1. click NextNext → 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.

Add a Mediator Component in the Composite Editor

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

    atwood-bpm-fig041
    Figure 20: 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.

Add SOAP Web Service Binding

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

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

    atwood-bpm-fig043
    Figure 21: Expose Composite as a SOAP service

  2. 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).

     atwood-bpm-fig044

    Figure 22: Generate WSDL

  3. From the Interface Type dropdown, select Synchronous Interface. In the Input section, click the + icon.
  4. Change the Message Part Name to employeeId → click OK. By doing this, a string that contains the employee id will be used as the input.
  5. Download the XSD that will be used for the output in the next step from here.
  6. 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 that was just downloaded → click OKOK → select the → EmployeeInfo element.

     atwood-bpm-fig047

    Figure 23: Select the Type for output

    Click OK OK. Verify that the input and output mapping matches what is shown below.

     atwood-bpm-fig048

    Figure 24: 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 diagram now has the three disconnected components in the three columns that will now be connected together.

  1. 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.

    atwood-bpm-fig050
    Figure 25: Connect the service to the Mediator

  2. 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 that were 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.

  1. Double click the HRServicesMediator. Because this is a synchronous operation, there is an input and output transformation that must be completed. This will be done in the next few steps
  2. Starting with the input transformation, because it is a simple mapping click the Assign Values button.

    atwood-bpm-fig053
    Figure 26: Assign Values button

    Fully expand (+) the in and the out, then map the two employeeId elements.

     atwood-bpm-fig054

    Figure 27: Map employeeId elements

    Click OK
  3. 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.

    atwood-bpm-fig055
    Figure 28: Transform mapping

    Click + to create a new XSLT transformation mapper file. Click OKOKOK. Expand the Source's Employees collection element.

  4. Map the source's Employees to the target's EmployeeInfo to automatically map very similarly named elements to one another. + Expand the if elements on the right and note that except for the phoneNumber, all of the elements that were automatically mapped.

    atwood-bpm-fig060
    Figure 29: Expand the if elements

  5. 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. Click OK.
  6. Back in the XSLT mapper, map the phoneNumber to ContactPhone. 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.

    atwood-bpm-fig064
    Figure 30: if automatically added in XSLT

    Click Save

Deploy the HRService to the SOA Application Server

  1. Deploy this project to the SOA Server so the composite application can be tested. In the Application Navigator right mouse click HRServiceDeployHRServiceNextNext → select the BPM Application Server → Next NextFinish.

Test the GetEmployeeById Web Service Using Enterprise Manager

  1. Open Enterprise Manager (e.g., http://localhost:7001/em) in a browser. Login using the weblogic credentials.
  2. Expand the SOA folder → default → select the new HRService.

    atwood-bpm-fig065
    Figure 31: Select the Composite for testing the Database Adapter composite

  3. 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).

    atwood-bpm-fig067
    Figure 32: Enter valid employee id

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

    atwood-bpm-fig068
    Figure 33: Successfully invoke service

    Now that the SOA composite that invokes the database adapter has been created, part 4 of this article will show you how the new service can be invoked from an Oracle BPM process.

    Part 4 - Invoke the Service Exposed from a Process in the BPM Composite Project

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

    1. Download the BPM Project that will be used to invoke the service from here. (This is a 12.2.1.2 project)
    2. Open the project and open the Candidate Approval process.
    3. To open the BPM Project's composite diagram, click the icon located in the top right corner of the process model diagram.

      atwood-bpm-fig070
      Figure 34: Candidate Approval process - open composite diagram

    4. Drag the SOAP Web Service component into the External References column in the Composite diagram.
      1. Name the service HRService (1, below).
      2. Click the Find existing WSDLs icon (2).
      3. Select Application Server (3).
      4. Click the dropdown and select the application server (BPM_Compact in this example) (4).
      5. Expand the composites as shown below until the previously deployed HRService service is displayed.
      6. Expand the composite labeled HRService [Default...] and select the GetEmployeeById service (5).

      atwood-bpm-fig073
      Figure 35: Define the HRService External Reference

      Click OK.

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

      Click OK OKSave.

    6. 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.

      atwood-bpm-fig075
      Figure 36: Add a Service Activity into the process

    7. Name the new Service activity Retrieve Candidate Information. Click the Implementation tab →change the Type dropdown to Service Call → click the Browse icon → click HRServiceOK(if HRService is not displayed, stop and restart JDeveloper)→click the Data Associations link.

      atwood-bpm-fig077
      Figure 37: Select the Service and open the Data Associations dialog

    8. With the Input tab selected, drag the employeeIdInput element on the left to employeeId on the right. Set the outgoing information by clicking the Output tabon the top→ +expand employeeInfo. On the right, +expand employee →+ expand employeeSummary.

      atwood-bpm-fig080
      Figure 38: Expand the output

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

      atwood-bpm-fig081
      Figure 39: Add XSLT Mapping to employee output

      Select employeeInfofrom the list on the left, then click the > icon to move it to the right.

      atwood-bpm-fig082
      Figure 40: Select employeeInfo for the XSL transformation to employee

      Click OK OKOKSave All.

    10. Drag EmployeeInfo on the left to EmployeeSummary (not EmployeeInfo) to the right. Click OKwhen the automap dialog appears. The XSL transformation should now look like this.

      atwood-bpm-fig084
      Figure 41: XSL Transformation for the output

    11. Save the project.

    Deploy the EmployeeOnboarding project.

    1. As in the 3rd section of this article, open Enterprise Manager and test the deployed composite. This time, instead of testing theHRServicecomposite, select the EmployeeOnboardingproject that was just deployed. Expand the parameters→ enter 101 in theemployeeIdfield→ click the Test Web Servicebutton. With theResponsetab selected, clickLaunch Flow Trace.

      atwood-bpm-fig087
      Figure 42: Response tab

    2. Select theCandidateApprovalprocess.

      atwood-bpm-fig088
      Figure 43: Select the process in the flow trace

    3. Select the dropdown and click Tree View.

      atwood-bpm-fig089
      Figure 44: Tree view Audit Trail

    4. Expand theAfter Service Callactivity → click one of the links to the right.

      atwood-bpm-fig090

      Figure 45: Expand the Script activity

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

      atwood-bpm-fig091
      Figure 46: Success!

    About the Author

    Oracle ACE Dan Atwood is a senior Business Process Management (BPM) architect and Director of Training for Avio Consulting, where he delivers Oracle BPM and Oracle PCS solutions for customers, taking them from concept through to implementation and delivery. Dan is certified as an Implementation Specialist in Oracle Unified Business Process Management Suite 11g, Business Process Management Suite 12c, and Oracle Application Development Framework 11g.


    This article represents the expertise, findings, and opinions of the author. It has been published by Oracle in this space as part of a larger effort to encourage the exchange of such information within this Community, and to promote evaluation and commentary by peers. This article has not been reviewed by the relevant Oracle product team for compliance with Oracle's standards and practices, and its publication should not be interpreted as an endorsement by Oracle of the statements expressed therein.