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