By Oracle ACE Dan Atwood
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.
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.
Figure 1: Data Sources
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.
Figure 2: JDBC Datasource
Figure 3: Test the connection to the database schema
Figure 4: Select SOA as the target
Having done this, the new data source should be in the list with these settings.
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.
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.
Figure 6: Database Adapter Deployment
Figure 7: Outbound Connection Pool
Click OK. You should see the following:
Figure 8: Outbound Connection Complete
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.
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.
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.
Figure 11: Create SOA Project
Figure 12: Add a Database AdapterThis automatically launches the Database Adapter Configuration Wizard. Click Next, name the new Database Adapter HR_Employee, then click Next.
Figure 13: Configure the database schema's connection
Figure 14: Enter the JNDI connection string
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.
Figure 16: Select the columns to be returned
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.
Figure 18: Select based on parameter
Click OK. This completes the SQL Expression Builder wizard.
Figure 19: Parameter query completed
Name the Mediator HRServiceMediator, leave the dropdown set to Define Interface Later selected → click OK to create the Mediator in the composite.
Figure 20: Add Mediator to the Composite
The next few steps expose the composite application as a SOAP web service.
Figure 21: Expose Composite as a SOAP service
Figure 22: Generate WSDL
Figure 23: Select the Type for outputClick OK → OK. Verify that the input and output mapping matches what is shown below.
Figure 24: Input and ouput for SOAP service
Click OK → click the copy wsdl and its dependent artifacts into the project checkbox. Click OK.
The Composite diagram now has the three disconnected components in the three columns that will now be connected together.
Figure 25: Connect the service to the Mediator
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.
Fully expand (+) the in and the out, then map the two employeeId elements.
Figure 26: Assign Values button
Figure 27: Map employeeId elementsClick OK.
Figure 28: Transform mapping
Click + to create a new XSLT transformation mapper file. Click OK → OK → OK. Expand the Source's Employees collection element.
Figure 29: Expand the if elements
Figure 30: if automatically added in XSLT
Figure 31: Select the Composite for testing the Database Adapter composite
Figure 32: Enter valid employee id
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.
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.
Figure 34: Candidate Approval process - open composite diagram
Figure 35: Define the HRService External Reference
Click OK →OK→Save.
Figure 36: Add a Service Activity into the process
Figure 37: Select the Service and open the Data Associations dialog
Figure 38: Expand the output
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.
Figure 40: Select employeeInfo for the XSL transformation to employee
Click OK → OK → OK→ Save All.
Figure 41: XSL Transformation for the output
Figure 42: Response tab
Figure 43: Select the process in the flow trace
Figure 44: Tree view Audit Trail
Figure 45: Expand the Script activity
Note that the call to the service was successful and data was returned to the process.
Figure 46: Success!
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.