Friday, January 11, 2008

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.

3 comments:

Anonymous said...

Hi,

Great blog on this problem. This fixed our issue.

If it wasn't for this blog I would have never found the cause to the problem. We were just cutting down the XML to make our call work!! Now with the XMLType we can always send the full blown XMl to the Oracle DB.

Thanks again...

Anonymous said...

Same here...we trimmed the XML to get it work

software development company in delhi said...

Nice blog and very informative thank you for sharing us.

Post a Comment