How to use Oracle Stored Proc with XMLType parameters in BizTalk 2010

Had a scenario in one of my projects where in we need to pass XMLType as input parameter and output parameter to a oracle stored procedure. So find some excerpts of the POC in this post.

Following are a few steps that I followed:

1) Created stored procs with XMLType as the input and output parameters.

Fig.1 Oracle Stored Procedure with XMLType parameters

2) Creating the schema's for the Oracle Stored Procedure using the Consume Adapter Service.

Fig.2 Consume Adapter Service

3) Following is the schema generated for the Oracle stored procedure.

Fig.3 BizTalk Auto Generated Schema

4) Created a simple orchestration for picking up the Request File and sending to the Oracle Stored Proc using the auto generated WCF request response port(This gets generated during the Consume Adapter Service).

Fig.4 Sample Orchestration

5) Sample file used for testing is as follows.

Fig.5 Sample File containing XMLType data as a String

6) Sample response file containing the response from the Oracle Stored Proc.

Fig. 6 Response File returned by Oracle Stored Procedure.

Overall, a couple of things to notice that while for using ambientTransactions we need to use a TNS alias. XMLtype is taken as a string so have to take care of the data that goes in the input. Reserved XML characters like '<', '>'. must be replaced with their entity.representations (&lt; and &gt;)