This article will give a short and easy explanation on how to use the WCF-Oracle adapter to retrieve a result set from an Oracle database.
The first thing we need to have on the Oracle database is a stored procedure that returns an object of the type ref-cursor.
This article will give some examples on how to program them. The second thing we need to do is to install an oracle client.
This has to be done so we can add an oracle service that can connect to the database, this service will be used later on to retrieve the metadata.
Installing the client will also add the necessary oracle drivers. In this example we added the service Oracleblog with the right settings for our database.
Now we can start developing.
The first step of the development is to generate the metadata and the request-response schemas.
We start by creating a BizTalk project in Visual Studio. After having created the project, we right-click the project and choose “add generated item”. Now we choose “Consume Adapter Service” to start the wizard. This new wizard was added in Visual Studio by installing the WCF-Oracle adapter.
In the wizard we first choose OracleDBBinding for “Select a binding” then we click “configure” to configure the database settings. The first thing we do is adding the name of the service we created in the oracleclient in the field DataSourceName on the pane URI. After this we can add the required credentials. Now making the connection by clicking “connect” will show all the databases available. We have to find the right stored procedure and add this to your BizTalk project. In our example we added the stored procedure P_SEARCH.
Adding it to your project will give you two things:
The request-response schema
The binding file for your send port
In this example two schemas are generated, this is because one schema is referring to the other. Both request and response schemas are created in the same XSD-file. Now we can create a simple orchestration in which we make a mapping to the request schema and create a request-response port so we can send the request and receive the file with the record set.
Now we deploy the project to add the orchestration to the BizTalk server. The next thing we do is creating the proper send-receive port for sending to the oracle database. We do this by importing the binding file that was generated with the schemas in to our BizTalk application. This will create the send-receive port with the correct action- and database settings. We now make a file receive and send port and bind this to our orchestration.
We are now ready to send the message.