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.

metadata

 
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.

 Oracle_SP_port


technorati tags :
Advertisements

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

%d bloggers like this: