@OracleIMC Partner Resources & Training: Discover your Modernization options + Reach new potential through Innovation

Database CRUD Web Services Enablement with Oracle Service Bus 12c

Victor Ameh
Fusion Middleware Migration Consultant

Before the release of Oracle Fusion Middleware 12c, we could only use  a JCA Database adapter with Oracle Service Bus 11g by working with Eclipse (OEPE) for OSB development of services and Jdeveloper in parallel as the  adapter wizards were only available in JDeveloper Oracle SOA Suite 11g project.

The development of services to decouple a database in Oracle Service Bus 12cis less work due to the now existing new Overview Editor in Jdeveloper 12c for developing Service Bus services compared to the Composite Editor for Oracle SOA Suite 11g, it lets you drag and drop components on to the editor to create services. The editor provides direct creation and editing of JCA adapters, REST bindings, and Service Bus services. See here for other significant feature changes with OSB 12c.

The going back and forth between the adapters in Jdeveloper and the OSB project in Eclipse (OEPE ) was quite cumbersome especially if the settings of the adapters need to be changed multiple times.

In this blog post, we
will show how to create webservice interface from database tables using the database JCA adapter in OSB 12c.


Use Case

We’ll use the HR Schema and create soap web services interfaces that exposes the CRUD
functionality on Employee table in HR schema.

First we create a service bus application in Jdeveloper:




Create the Database Adapter

To create an outbound Database Adapter , we drag the
Database Adapter to External Reference swimlane from the Service Bus component in the Component window
to create the Database adapter jca artifacts


Give a name to the service

In the Service connection , create a connection …


In the Create Database Connection , provide the database
connection details










In Define Selection Criteria, we’d create a parameter
“employee_id” and edit to create or Edit
a Toplink Expression…


Screen Shot 2015-05-27 at 4.54.13 PM





After the project is
created , the following artifacts are created in the Resources folder.


Creating the Business Service

For outbound
adapters, we will a business service configured to use JCA transport. If it ‘s an inbound adapter a proxy service is used.

In our case, we’d
create the business service from the jca
configuration file created earlier. Right Click on the .jca file |Service Bus| Generate Business Service.


The Create
Business service wizard launches. Specify the name ..


Specify the directory and name where the the WSDL should be
created. I created a folder BusinessService for better organisation
my project artifacts.


Accept defaults and click next until finish. Note
that the .biz and wsdl file of the services are created in the specified folder
and ready to be used.

We would go ahead to test the service from the OSB console.

Testing the database service from OSB

We need to
deploy the OSB project to the integrated
weblogic as used in our case. In order for the project to successfully deploy,
create Datasource and DBAdapter Connection Factory in weblogic as described in here. (Section 9.5).
To deploy we select Service bus project name, right
click |Deploy|ServiceBusprofile1( you create a New Deployment profile if it is the first deployment) as
in the figure below:


Next to deploy to Sevice Bus Server


Click Next to select IntegratedWeblogicServer
as Server


Click Finish
to complete the deployment of the service bus project .


Launching the
OSB console , we expand the SBproject |Business service. Click on the the getEmployeeDetails_db.jca and in the actions colum , we can launch the
test console.


We’d test getEmployeeDetailsSelect operation of
the business service and test with a sample employee id.



The result
shows the services retuning the employee details from the database.


In this
tutorial, we saw how to integrate JCA Database adapter with OSB 12c. Notice the
response from the database contains a collection of employees and the interface
is tightly coupled to the database.
In a next
blog, I’ll show how to use a proxy
service to make the service interface a and loosely coupled with the database.

Facebook Google+ Twitter LinkedIn Pinterest Delicious Digg Addthis

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.