Tuesday, June 5, 2012

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

No comments:

Post a Comment

Your comments will help improve my posts