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.