How to invoke sql query from OSB - Pass through

In this post we will look at how to execute a simple select query.

I will break this into two posts when in the first post I will explain how to make a OSB pass through and in the next post I will explain how to use transform inside proxy service or some logic before invoking the business service.

Below is the SQL

Step 1.) Go to JDeveloper and drop a DB adapter on the reference side do the needful to create the sql as below

JDeveloper will create below file which we will copy to OSB project in Eclipse.

Copy the .xsd, .jca, .wsdl, mappings.xml and properties.xml file to eclipse.

Now we will create a business service which will invoke this sql,to do this we will right click on .jca file and give name to business service.

The business service is done, this can be deployed and tested individually, however we can't expose it and use it as a web service, this is like a DB adapter on the composite without any BPEL or Mediator to expose it.

Lets deploy this to OSB server and test it.

So half of our task is completed, now from here we can proceed to create our OSB pass through.

OSB pass through: this is used when we directly expose the business service with the help of proxy service (no transformation).

Right click on business service > Oracle Service Bus > Generate Proxy Service

We are done with the proxy service, deploy and test it.

Introduction to OSB

This is my first post on OSB, being a SOA developer OSB is always confusing, I will try to present OSB from the eyes of a Oracle SOA Developer.

OSB is stateless.

1. This means their is no SOA_INFRA schema where all the activities are getting logged, being a SOA developer we know whats the importance of audit trail when we have to debug.

2. We don't have asynchronous process in OSB, for the obvious reason as there is no place where we can dehydrate an instance and invoke it back when we receive the response.

OSB Vs SOA

1. As their is no logging in the DB (SOA_INFRA), which makes it very fast, according to claims its 2.5 times faster in performance then SOA. I say everything happens in air in OSB.

2. OSB is a perfect technology where performance is mainly important.

OSB Vs Mediator

From my experience I have found mediator is nowhere faster then BPEL which is theoretically told, so it does not stand anywhere near to OSB.

Mediator can only connect different web services or adapter, however we can not put any orchestration logic.

So OSB is BPEL plus Mediator and is 2.5 times faster but has no audit trail.

Basic components of OSB

There are only two components in OSB which do all the magic, Business Service and Proxy Service

Business Service:

Business Service is adapter of SOA.
We have separate adapters for web service, database, JMS, file/ftp, rest etc, in OSB business service acts as single adapter to do absolutely everything and can do actually more then SOA adapters.

Proxy Service:

Proxy service is BPEL activity of SOA.
All the orchestration logic is written inside proxy service.