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

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 void setAccount(String account) {         this.account = account;     }     public String getAccount() {         return account;     } } public class Party {     public Part
Read more

Passing/Receiving HTTP header in SOA BPEL

Image
In this example we will explore how to pass or receive the parameters from HTTP header. We will explore below two scenarios, To receive the HTTP header from SOAP request. To pass the HTTP header while calling soap service. SOA version used: 12.1.3c To receive the HTTP header from SOAP request: First we need to add the property “oracle.webservices.http.headers” under binding.ws in Service element in composite.xml. This property contains the comma separated parameters that we are expecting in request. Example,  ENV,VERSION. <binding.ws port="http://xmlns.oracle.com/SOA_Learning/HTTPHeader/HTTPHeaderTest#wsdl.endpoint(httpheadertest_client_ep/HTTPHeaderTest_pt)">          <property name="oracle.webservices.http.headers">ENV,VERSION</property> </binding.ws> Now in BPEL source under receive activity, we need add <fromProperties> element which will copy the parameters from HTTP header to BPEL variable.
Read more

Integrating Weblogic with IBM MQ over JMS

Image
We can integrate weblogic with IBM MQ over JMS by creating Foreign JMS server with Links in weblogic server. We need to create .binding file which will acts as file based JNDI provider to create JMS binding to MQ resources. Will first explain how to create .binding file and then will configure weblogic to use the same. Steps to create .binding file: Firstly we need to add below jar’s in CLASSPATH jms.jar com.ibm.mq.jar com.ibm.mqjms.jar jta.jar connector.jar jndi.jar providerutil.jar fscontext.jar Create JMSProperties.config file with below properties INITIAL_CONTEXT_FACTORY=com.sun.jndi.fscontext.RefFSContextFactory PROVIDER_URL=file:/tmp/mqm/jndi SECURITY_AUTHENTICATION=none Run below command ./JMSAdmin -v JMSProperties . config InitCtx> def qcf(ForeignCF) TRANSPORT(CLIENT) HOST(localhost) PORT(1414) CHANNEL(MQCHANNEL) QMGR(MQMRG) def q(MQ1) qmgr(MQMRG) queue(MQ1) def q(MQR1) qmgr(MQMRG) queue(MQR1) display ctx
Read more