BizTalk Oracle Adapter / ADO.Net Limitation (ORA-01460: unimplemented or unreasonable conversion requested)

System defined exception
Call to RP_BIZTALKTEST@OracleDb://ORADEV/DBO/Procedures/TopLevel
Implementation = OracleDb://ORADEV/DBO/Procedures/TopLevel

Source: OracleDb
Error Code: 1460 (0x5b4)
Cause: OracleDb://exception=DBException (Unique ID )
42000 : [Oracle][ODBC][Ora]ORA-01460: unimplemented or unreasonable conversion requested


Exception data:
struct DBException =
WideString StatusCode = "42000"
WideString Description = "[Oracle][ODBC][Ora]ORA-01460: unimplemented or unreasonable conversion requested"

I'm using BizTalk 2006 with BizTalk Oracle Adapter to Oracle9i Database.

What i was trying to do here is to call an oracle procedure to insert a new row in a table and one of the field is CLOB/XMLType. This field is actually storing the current message which was passed in to the orchestration and to be used in future processing (other orchestration).

For simple / small size message, it was working fine as expected. Fortunately, I did try the scenario for huge incoming message (always test your code with the highest value of the assumptions (e.g. The user will be able to submit 1000 items in 1 transaction). Then I got this exception from BizTalk.

I tried to search through the net about this, it seems there is a limitation to pass in a parameter more than 32 KB size. (http://www.google.com/search?hl=en&q=32+kb+xmltype+parameter+procedure+limit&btnG=Search&meta=)

Some Tests which I did :
1. Use NativeSQL insert into table. - Not Working
XMLType column will be generated as base64binary type in the schema.
I have tried to pass the xml raw string but failed. Same if I tried to convert the xml into base64binary.
The error message which was returned :

System defined exception
Call to Insert@OracleDb://ORADEV/DBO/Tables/BIZTALKTEST
Implementation = OracleDb://ORADEV/DBO/Tables/BIZTALKTEST

Source: OracleDb
Error Code: 932 (0x3a4)
Cause: OracleDb://exception=DBException (Unique ID )
HY000 : [Oracle][ODBC][Ora]ORA-00932: inconsistent datatypes: expected NUMBER got BINARY


Exception data:
struct DBException =
WideString StatusCode = "HY000"
WideString Description = "[Oracle][ODBC][Ora]ORA-00932: inconsistent datatypes: expected NUMBER got BINARY
"

Or (I tried some combinations for the data type : CLOB/XMLTYPE)

Error transmitting message: SOAP-ENV:ServerRequest ID: Unknown
Exception Type: A cross object system conversion error occurred
Exception Info:
Exception occurred:
E-CORE0053: Dimensions or bounds of source and target params do not match
Call to Insert@OracleDb://ORADEV/DBO/Tables/BIZTALKTEST
Implementation = OracleDb://ORADEV/DBO/Tables/BIZTALKTEST

SOAP://xmlns/schemas.microsoft.com/BIZTALKTEST/InsertRecord:Type/[]/TESTRAW ==> OracleDb://ORADEV/DBO/Tables/BIZTALKTEST/InsertRecord/VARCHAR2/WideString
(SOAP://xmlns/schemas.microsoft.com/BIZTALKTEST/Insert:Element/Rows ==> sequence<struct OracleDb://ORADEV/DBO/Tables/BIZTALKTEST/InsertRecord&>)
(Error occurred before call to foreign method)


2. Call oracle Procedure passing XMLTYPE parameter - Not Working
I tried to use XMLType as a parameter but there was an error similar to the 2nd error above.

Error transmitting message: SOAP-ENV:ServerRequest ID: Unknown
Exception Type: A cross object system conversion error occurred
Exception Info:
Exception occurred:
E-CORE0053: Dimensions or bounds of source and target params do not match
Call to RP_BIZTALKTEST@OracleDb://ORADEV/DBO/Procedures/TopLevel
Implementation = OracleDb://ORADEV/DBO/Procedures/TopLevel

SOAP://xmlns/schemas.microsoft.com/TopLevel/RP_BIZTALKTEST:Element/V_TESTRAW ==> OracleDb://ORADEV/UNDEFINED_2000/WideString
(SOAP://xmlns/schemas.microsoft.com/TopLevel/RP_BIZTALKTEST:Element/V_TESTRAW ==> OracleDb://ORADEV/UNDEFINED_2000)
(Error occurred before call to foreign method)

3. Call oracle Procedure passing CLOB parameter and then convert into XMLTYPE when insert - Not Working
This is the one that actually has 32KB limitation. I actually created a small .net application using oracle client to test this and turned out that I got the same error when i tried to pass > 32 KB string into the parameter. There is a work around in this article, but i find it more complex than the 4th solution below (http://msdn2.microsoft.com/en-us/library/cydxhzhz(VS.80).aspx)

System defined exception
Call to RP_BIZTALKTEST@OracleDb://ORADEV/DBO/Procedures/TopLevel
Implementation = OracleDb://ORADEV/DBO/Procedures/TopLevel

Source: OracleDb
Error Code: 1460 (0x5b4)
Cause: OracleDb://exception=DBException (Unique ID )
42000 : [Oracle][ODBC][Ora]ORA-01460: unimplemented or unreasonable conversion requested


Exception data:
struct DBException =
WideString StatusCode = "42000"
WideString Description = "[Oracle][ODBC][Ora]ORA-01460: unimplemented or unreasonable conversion requested"

4. Use Oracle Data Provider for .NET 9.2.0.7.00 (ODP.Net) - Working
I found that this is actually the solution and the easiest way to do, passing a XMLTYPE is just as simple as passing other parameters, It has its OracleDb Type.XmlType. So far I have tested with huge messages more than 32 KB and they are working fine. Please also note on the ODP.Net version.

OracleParameter pContent = new OracleParameter("content", OracleDbType.XmlType, content.Length);

Before I decided to go with the ODP.Net, I have tried using :
1. ODBC Connection (System.Data.Odbc) : Limited with 4 KB size
2. .Net Oracle Data Provider (System.Data.OracleClient) : Limited with 32 KB size as well.