Executing Stored Procedure using BizTalk Oracle Adapter

Stored procedures from the Oracle adapter can only be called when they are within a package, this is due to the Oracle driver that does not provide the list of parameters. Visit Microsoft MSDN to read more about Oracle adapter calling the stored procedure.

Limitations of Oracle adapter calling a stored procedure wrapped in a package

For procedures, large-object (LOB) types are supported as IN parameters only. When they appear as INOUT, RETURN and OUT, the procedure is not displayed in the browser and therefore is not callable. LOBs are supported in tables in the Insert, Update, or Query methods.

In this article we will assume the package containing the procedure as stored procedures. In order to call a stored procedure wrapped up in a package generate the metadata of the stored procedure as shown below.

 
The schema containing all the types of the package, port type and multi-part message will be generated. In order to construct the request message select the root-reference property of the schema generated to the desired Request Message. Construct the message in a message assignment shape by loading the xml into an XMLDocument variable. The oracle adapter creates request, response and exception type messages and schema. Create a new solicit request-response port in the orchestration and select the exiting port created in the orchestration as below.

 

codeproject
technorati tags : BizTalk

How to Select, Insert, Update and Delete in BizTalk using Oracle Adapter

Select, Insert, Update and Delete statements can be executed using the BizTalk Oracle adapter by generating the Oracle Adapter metadata for a specific table. Right click the solution and click add generated items and click “Add adapter metadata”. A schema along with multi-part message types and port will be created along with an Orchestration file. For creating Oracle adapter metadata see Generating MetaData From Oracle Adapter.

Multi-part messages for Selecting (Query, QueryResponse), Update(Update, UpdateResponse), Insert(Insert, InsertResponse) and Delete(Remove, RemoveResponse) will be created with a message part named parameter for each message.

You can generate requests for each Request messages by changing the Root Reference property of the schema to the request message you want. Generate the instance and load the xml into the XMLDocument type variable with the SQL Statement in the message.

Bulk Insertion is an advantage using Oracle adapter and if you have several records to be inserted or updated you can create their messages and send it to the oracle adapter. In the response message the rows affected is returned.

For Select, Update and Delete you have to fill the Filter tag. For filters we supply the where clause just as in any other SQL Statement. For e.g. If we want to select particular records we give “columnname1 = value and columnname2 = value”.

• Select and Remove Type multi-part message only requires the filter parameter to be filled.

 

• For Update we supply the values of the columns that have to be changed in the request message along with the filter parameter.

 

 

• Insert type messages only require records to be inserted without filters.

 
Create a new port with solicit request-response type. Select the existing port type which was auto-generated with the metadata created.

 

codeproject
technorati tags : BizTalk