X

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

How to Service Enable Database Stored Procedure or Function in Oracle Service Bus 12c.

Victor Ameh
Fusion Middleware Migration Consultant

Oracle Service Bus transforms complex and brittle architectures into agile integration networks by connecting, virtualizing, and managing interactions between services and applications. In this post I will explain how to
create a simple web service in Oracle Service Bus 12c based on functions or stored procedures in a database.

Service Bus provides a J2EE Connector Architecture (JCA) transport that interacts with back-end Enterprise Information Systems (EIS), letting these systems participate in the Service Bus integration environment. The JCA transport provides native connectivity between Service Bus and external systems, letting those systems interact in the service bus layer and leverage the capabilities and features of Service Bus. For our purposes we will be using JCA Database Adapter, which lets Service Bus business and proxy services communicate with Oracle databases or third-party databases through JDBC.

This service decouples your database and
shows how to easily integrate OSB 12c with databases in your SOA environment.

Prerequisites

Use Case

We will expose the web service of the ADD_JOB_HISTORY
Stored Procedure in HR Schema from Oracle Service Bus 12c, we can test this by updating
job history of an employee in the HR Shema in our database from the service.

Getting Started

We create a New | Project from out
application perspective in Jdeveloper.

blog3Img1

We will select Service Bus Project

blog3Img2

We will give a project name and then
Click Finish.

blog3Img3

blog3Img4

Next we drag the Database adapter on the
external services of the project and call it addJobHistory.

blog3Img5

In the Service Connection dialog, we will select a database connection
already defined for our project.

blog3Img6

blog3Img7

Select the ADD_JOB_HISTORY Stored
Procedure in the HR Schema.

blog3Img8

We will accept the defaults for the next
steps and click Finish.

blog3Img9

blog3Img10

blog3Img11

blog3Img12

The result will be an Oracle Service Bus
Business Service (.biz) created by the Database adapter.

blog3Img12_1

Deployment and Testing

We can test the business service to
confirm that the service can actually execute the ADD_JOB_HISTORY Stored Procedure in the HR Schema.

Before deployment to Weblogic, we would
have created the Datasource and Connection Factory as in this blog.

blog3Img13

The Business Service successfully
executes with no response as the Stored Procedure has no output parameter.

blog3Img14

Next we confirm by running a select query
on our JOB_HISTORY table. The Update appears in the records.

blog3Img15

To create a Proxy service to decouple
your database, see my previous blog. Hopefully my series of posts has assisted you getting more comfortable with Web Services through Oracle Service Bus 12c. For more samples and guides check out the Oracle Service Bus Learn More section and the SOA section on Oracle Learning Library

Facebook Google+ Twitter LinkedIn Pinterest Delicious Digg Addthis

Join the discussion

Comments ( 2 )
  • guest Friday, July 3, 2015

    Hi Victor.

    Thanks for the post, it is very helpful.

    I would like to ask you the following, would not it be possible to create a web service based on pl/sql package and then create SB business service?

    If we follow this path, what would be the advantage over the way to create similar service with OSB? By advantage I mean the response times?


  • Nomadus Sunday, July 5, 2015

    Hi Victor.

    Thanks for the post, it is very helpful.

    I would like to ask you the following, would not it be possible to create a web service based on pl/sql package and then create SB business service?

    If we follow this path, what would be the advantage over the way to create similar service with OSB? By advantage I mean the response times?


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