How to create a service to query from the database table in OSB 10gR3.

To achieve the task we have to follow below steps –

Create a Database Adapter

  • Create a database connection in Jdeveloper.
  • Create a database adapter with in BPEL project or normal empty Jdev project in Jdeveloper.
  • This database adapter uses JCA transport protocol.
  • While creating DB adapter refer to the db connection that we created in Jdeveloper.
  • When we select the db connection, the JNDI name will automatically populate. If we want to use Connection factory and data source defined with in the server, we need to modify the JNDI value (ex: eis/DB/dbConection1) with the corresponding JNDI data source name from the server.
  • Complete the creation of database adapter. By now Jdeveloper will create Schema, WSDL and Toplink mapping files for the selected table.
  • Now save the project.

Create a project in OSB Console

  • Connect to the OSB Console and under Project Explorer create a new project.
  • Create Schema file, WSDL file and Business service.
  • While creating Business service, we can select Managed or Non Managed connection type.
  • If we select Managed, the project will use the data source, which is defined with in the server to connect to the database.
  • If we use Non-managed, the project will use the connection settings that were created from the database connection in Jdeveloper.
  • In production it will always need to use the Managed connection type.
  • You can test the project once after we create the business service itself.
  • Once the testing if successful, if we want to expose this web service to the client, we need to create a proxy service. The proxy service will expose this Business service to the client.Service Invocation Flow

Create a data source in OSB Console

  • In web logic server home page, select Data Source link.
  • Create a new data source, while doing provide the data source name (ex: MyDB) and JNDI name (ex: eis/DB/dbConection1). This JNDI name should match exactly as we given while creating the database adapter in Jdeveloper.
  • While creating the data source at final step add this data source to the Admin Server.
  • After we complete creating the data source, we need to create the Connection Factory in the server.
  • While creating the connection factory, we need to use the same JNDI name (ex: eis/DB/dbConection1) what we have used while creating the data source.
  • Once after we create the connection factory, select it and you will see the available data sources in the display. As while creating the data source we have used the Oracle database with XA versions, so select the check box for XADataSourceName and on the next column select and you will see an option to enter text. Here type the data source name what we have specified while creating the data source (ex: MyDB) and click Enter button. Only after we click the Enter button, the value will get saved. Now click on the SAVE button to save the selected data source name in the connection factory.

Now the connection factory is set to use the data source that we created.

If we miss any of the above steps while reading the data source or connection factory, the connection will not be successful while executing the web service which in turn returns error messages.

Note: We can create the same Business service from Web logic workshop and create a server connection from the workshop to deploy the OSB project into OSB console. After deploy we will see all the Schema, WSDL, Business and Proxy services in the OSB console.

This entry was posted in Oracle Service Bus (OSB) and tagged , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s