Oracle SOA DB Adapter Fails to return XML Type with MS SQLServer

When you use DB Adapter to access Microsoft  SQL Server Stored Procedures that return output  with XML types, a class type mapping exception can be thrown.

A stack trace similar to the below one can be thrown from the adapter.

Exception occured when binding was invoked. Exception occured during invocation of JCA binding: "JCA Binding execute of Reference operation 'Test' failed due to: Unimplemented string conversion. Conversion of JDBC type to String is not supported. An attempt was made to convert a Java object to String using an unsupported JDBC type: . Use a data type with a supported JDBC type. ". The invoked JCA adapter raised a resource exception. Please examine the above error message carefully to determine a resolution.

To fix this, change the driver from “com.microsoft.sqlserver.jdbc.SQLServerDriver “  to “weblogic.jdbc.sqlserver.SQLServerDriver “ by logging to the console and changing the driver class name or create a new connection by using of Oracle’s MS SQL server driver.

Also make sure to create a strong schema by introspecting the stored procedure using some input values. If weak schema is used, XML results greater than 2,033 characters in length will be returned  in multiple rows of 2,033 characters each.

For example,

CREATE PROCEDURE [dbo].[spSoaTest] @param1 int AS BEGIN SELECT TOP 500 FROM dbo.TableXX tbl ORDER BY  1 DESC  FOR XML PATH('test'),ROOT('test1'),TYPE END

If you omit the "type" after the "for xml" you may get the below exception while introspecting the stored procedure in JDeveloper.

BINDING.JCA-11819

Database type not supported.

Encountered a database type ntext that is either not supported or is not implemented.

Parameter XML_F52E2B61-18A1-11d1-B105-00805F49916B is of type ntext which is either not supported or is not an implemented datatype.

Check to ensure that the type of the parameter is one of the supported datatypes or that there is a collection or user defined type definition representing this type defined in the database.

                at oracle.tip.adapter.db.sp.xsd.sqlserver.DatabaseBrowser.expandParameter(DatabaseBrowser.java:88)

                at oracle.tip.adapter.db.sp.xsd.sqlserver.DatabaseBrowser.expandParameters(DatabaseBrowser.java:65)