Connect OSB (Oracle Service Bus) to Oracle Database using Weblogic Datasource

In this post I will try to explain how to connect OSB to Oracle Database with out using DB adapter. With Weblogic datasource we can directly execute the SQL select queries. We will first create weblogic datasource.

Below are the steps to create Weblogic DataSource:
  1. Login into Weblogic console. eg. http://localhost:7777/console
  2. Go to Services->Data Sources Click on  new generic datasources
  3. Give Data source name, JNDI name, Database Type.












4. Click next
5. Select Oracle Driver and click next.
6. Give database connection details ie. ServiceName, IP, Schema, Password, port.












7. Click Next, and click Test Configuration. If everything is okay then you will see "Connection test succeeded." message. Click on Finish
8. Next Click on Data Source 'OSBDB' and click on Targets. Select admin server and click save.  You have a data source ready to use.







Next,

We will show how to use this data source in OSB.

1. In message flow of the proxy service, create a variable "query" and assign the query you want to execute. eg. 'select servicename, description from servicemaster' 
2. Use fn-bea:execute-sql() to execute the query.
eg. 
fn-bea:execute-sql('OSBDB','Resp',$query)
  •     OSBDB- JNDI name of the data source created above.
  •     Resp- is the Qname ie. the root element name for each row return from database.
  •     $query-  Is the actual query you want to execute.

Below is the sample response of the above query:







If you need more details on fn-bea:execute-sql() refer below link:


Thanks,
Rohan

Popular posts from this blog

API Design First approach: Implementing quick mock API's using swagger hub and postman

Image
Consider a scenario, where you as a API developer been ask to to implement the API's for a new mobile/web application team. The requirement is to implement a application to allow users to manage orders.         Mobile APP -> API infra->Backend enterprise system While implement a full working API might take days, you also do not want the application team to wait till the API's are ready. Thus, in most case we end up implementing the mock API's with sample scenarios. In this post, we will define an API using swagger openapi 3.0 yaml specification in swaggerhub. We will then use the definition to implement mock API's in postman. Even though postman provide option to define the API's, i liked swagger hub much better, it allow you to view your API on right side.  For this post I implemented API definition in swagger, please find the below link. https://app.swaggerhub.com/apis-docs/lopesrohan1988/ordermanagement/1.0.0 API Definition can be found at below link: h...
Read more

Combine or Merge XML documents in Single XML using Boomi & Groovy

Image
In this small post will try to show how you can merge multiple XML Documents in Boomi.  Consider following scenario where you have to merge Document 1 & 2 in a new Document 3 Document 1: <?xml version="1.0" encoding="UTF-8"?> <tns:OutputParameters xmlns:tns="http://sample.com">    <tns:Book>       <tns:Names>          <tns:Name>XZY World</tns:Name>       </tns:Names>       <tns:Authors>          <tns:AuthorID>12354</tns:AuthorID>       </tns:Authors>    </tns:Book> </tns:OutputParameters>   Document 2: <?xml version="1.0" encoding="UTF-8"?> <tns:OutputParameters xmlns:tns="http://sample.com">    <tns:Book>     ...
Read more

JAVA embedding in Oracle SOA 12c

Image
In this post we will explore how to EMBED Java or call Java API from Oracle SOA BPEL process. In order to demonstrated this, I have created a simple JAVA project with 3 classes. 1.        Account.java -> This class represent Account DTO       2.       Party.java -> This class represent a party (customer) DTO       3.       GetNumber.java -> This class contains the method that takes Party DTO as input and apply some business logic and return party’s AccountDTO in response. public Account getNumber(Party party) The above classes are created to simulate the calling of actual business logic API’s. Account.java Party.java GetNumber.java public class Account {     public Account() {         super();     }     String account;     public v...
Read more