Enter your keyword


REST Service with DB adapter Oracle Service Bus

In the previous blog, we have explained, how to create simple REST service without DB adapter. Please follow the blog to get the basic understanding of how to create simple REST service. 

In this blog, we will extend the blog further and be going to explain how to create REST service with DB adapter and provide the response in XML/JSON both.

You must have JDeveloper 12.1.3 and Oracle database 11g installed on your machine to complete this example.

After this blog, you should have learned to:

1) How to create SB(Service Bus) application with SB project
2) How to create REST service
3) How to create Business Service(DB Adapter in this example)
4) How to call Business Service

Download sample project from here

Let’s get started.

Create the Service Bus application with Service Bus Project.

Create the folder structure like below. We have create a folder as per the standard.

Double click the RESTServiceProject composite

Right click on the External Services Swim lane and select Insert Adapter -> Database

Enter Name DBReference and select Service directory to Resources->Services->Business and click Next

Enter the JNDI (eis/DB/hrds) name that you have created in the WebLogic console and select Connection.
Follow the blog for datasoruce and JNDI creation.


Click Next, select Perform an operation on a Table Radio button and click Select checkbox only. In this blog, we have taken this option, as we want to select data from the table. However, we can opt different operation as per the requirement.

Select Import tables, select Employees table and click OK -> Next



Remove relationship and click Next twice



We will select only records belonging to the specific department. To do so, we have to create a binding variable(deptId) so that we can pass the value in department id field during service calling. To make it happen, click on Add button, enter deptId and click OK

Now click Edit button. A dialog will get open, click on Add button, click on Edit button from First argument section and select departmentId field.

Click Parameter radio button from second argument section and select deptId and click OK

Click Next three times and Finish.

Now Move the the wsdl to WSDLS folder, jca file to JCA folder, xml file to XMLs folder, xsd files to Schemas folder.

To move any file, right click the file -> Refactor -> move

Business service is ready. We can test the Business Service independently. Right click on the DBReference and click Run

Enter deptId and click Execute button


If the response comes with matching department id you entered, it means Business Service is OK and we are ready with our DB adapter(Business Service). 

Let’s proceed further.

Right click on the Proxy service swim lane and select REST adapter

Click on + sign from Operation Bindings section and select Add operation binding

Rename operation name to getEmp, select GET from HTTP verb  and click on browse from schema file icon, choose request element from Employees.xsd and click OK

Click on Response tab, select XML and JSON checkbox both and click on browse from schema file icon, select choose Employees.xsd -> EmployeesCollection and Click OK

Click OK twice.

We are ready with REST adapter. Move the wsdls and proxy files into WSDLS and Proxy folders respectively.

Let’s proceed further with Pipeline.

Right click on the Middle swim lane and select Insert->Pipeline. Select the Pipeline folder by choosing the Browse button

Select WSDL radio button, select RestService.wsdl, click OK. Uncheck the Expose as a proxy service checkbox and click Finish

Wire the RestService with RestPipeline

Now create XSLT transformation to transform the business service output.

Create a folder XSLTs under resource folder. Right click on the XSLTs folder -> New -> XSL Map

Enter file name EMP.xml,  Select Browse button from Primary source section, Select Browse, Choose DBReference_table.xsd and select EmployeesCollection element. Click OK twice.


Select Browse button from Target section. Select Browse, Choose Employees.xsd and select EmployeesCollection element. Click OK twice.

Now click OK

XSLT file will get open. Right click on the Employees from right panel, Add xslt instruction -> for each and wire the elements like below:

Double click on the RestPipeline from middle pane and drop pipeline pair node from component pallet

If you want to call any external service, OSB gives you  the Service Callout component to call business service we created.

Drop Service Callout component from component pallet in the Stage1 under Request pipeline. Open the Service Callout properties and click browse button and select DBReference.bix

Enter variable name(serviceInput ) in Request payload and variable name in Response(serviceOutput ) payload. serviceInput variable will be used to assign the request payload and whatever business service response will come will be assigned in the serviceOutput variable.

Let’s see how these variable will be used in the below steps.

Drop Assign activity under Service Callout and configure the properties like below. Enter variable name serviceInput and enter the value in expression:

<dbr:DBReferenceSelect_deptIdInputParameters xmlns:dbr="http://xmlns.oracle.com/pcbpel/adapter/db/top/DBReference">

Note: This is DB adapter input and you can get the same when you run the DBAdapter independently as we did in the previous step

Drop the Replace activity in Stage1 under Response pipleline. Enter the below value in Replace properties:

a) Location: body
b) Expression : .
c) Value: Select XSLT resource, browse Emp.xsl and enter $serviceOutput in Input Document Expression, click OK
d) Replace option: Select Replace node content radio button

REST service is done, now we are ready to test the service:

Go the the composite again, right click on the RestService and click Run

Enter deptId and click Execute


We can see the response in the XML format. If you want the response in json, go back, select application/json from the Accept drop down and click Execute.

Some helpful example you can look into:



Consume REST service in Oracle Service Bus


Poll database using DB adapter in Oracle Service Bus


Secure REST service in Oracle Service Bus using OWSM(Oracle Webservcie Manager)


Some Toughts (12)

  1. added on 11 Jan, 2017

    Its realy nice blogs and i am very thankful to you Because i am looking for this type of step by step Application development process blogs and this is very helpful for me and i think whose all peoples who are want to learn OSB..Thank You so Much sir…And i am waiting for more blogs….

  2. added on 11 Jan, 2017

    Thanks Sir,
    ThIs blog is very helpful for me……………

  3. added on 11 Aug, 2017

    Hello Sir,

    i would like ask about this one
    "Drop Assign activity under Service Callout and configure the properties like below. Enter variable name serviceInput and enter the value in expression:


    im still don't get your point, would you like show me step by step?

    thank you very much, GBU!

  4. added on 15 Aug, 2017

    Dear Christian,

    I mean to say, we have configured Service callout to call business service, now we want to assign input to the service. So we have one activity called Assign that needs to drop under Service Callout activity. This assign activity will ask two variables one would be input and other would be output. So i kept the name input variable as serviceInput and assigned the input payload required by service callout.

    Please download the OSB project and see how it has been configured.


  5. added on 18 Aug, 2017

    Dear Mr. Ankur

    btw how can i get value like below this without "run dbadapter"?


    cause my local (laptop) for weblogic get some trouble.


  6. added on 18 Aug, 2017

    Hi Christian,

    Click on Assign activity -> Go to assign properties -> Value -> Expression

    And expand the body -> request parameter and assign the value.

  7. added on 18 Aug, 2017

    Hello Mr. Ankur

    i've lot of questions. may i know your private email?
    mine is christianys14@gmail.com.


  8. added on 18 Aug, 2017

    Please send all your queries you have..


  9. added on 19 Aug, 2017

    all my query is simple one, just like

    select * from student where id = xid.
    btw, first i created BPEL and it's work fine and give return value.

    when i'm tried with OSB, i get some error below this

    [2017-08-18T01:35:48.419+07:00] [WLS_OSB1] [NOTIFICATION] [] [oracle.osb.logging.pipeline] [tid: [ACTIVE].ExecuteThread: '6' for queue: 'weblogic.kernel.Default (self-tuning)'] [userId: ] [ecid: 1d7fc413-43c5-4d1e-8dc6-0575f54deb73-0000736f,0:3] [APP: Service Bus Test Framework] [partition-name: DOMAIN] [tenant-name: GLOBAL] [FlowId: 0000LrlUi523R9GqywR_6G1PYHgE00006E] [RouteNode1, null, null, REQUEST] REQUEST = 1075
    [2017-08-18T01:35:48.519+07:00] [WLS_OSB1] [ERROR] [OSB-382191] [oracle.osb.pipeline.kernel.router] [tid: [ACTIVE].ExecuteThread: '4' for queue: 'weblogic.kernel.Default (self-tuning)'] [userId: ] [ecid: 1d7fc413-43c5-4d1e-8dc6-0575f54deb73-0000736f,0:3:3] [APP: Service Bus Test Framework] [partition-name: DOMAIN] [tenant-name: GLOBAL] [FlowId: 0000LrlUi523R9GqywR_6G1PYHgE00006E] OSB_MHS_CRUD/Pipeline/Pipe_Select: Unhandled error caught by system-level error handler: com.bea.wli.sb.pipeline.PipelineException: Transport error (error code=OSB-380001): Internal Server Error[[
    com.bea.wli.sb.pipeline.PipelineException: Transport error (error code=OSB-380001): Internal Server Error

    any idea? thanks!

  10. added on 25 Jan, 2018

    Thanks For sharing nice article about database development services.
    Database Development Services in India

  11. added on 7 May, 2018

    Thanks for sharing this amazing blog
    Oracle SOA Online Course

  12. added on 14 Jan, 2019

    Hi Raghu,

    I am facing an error which I am not able o solve after migrating one of the 11g service to 12c
    Below is the error message

    com.bea.wli.sb.pipeline.PipelineException: the value specified for binding or creation is null

    Could you please help? what might be he cause of the issue?

Leave a Reply

Your email address will not be published.