BizTalk Oracle Adapter Vs WCF-based Oracle DB Adapter

The following is a list of key feature differences between the BizTalk Oracle adapter and the WCF-based Oracle DB adapter in the BizTalk Adapter Pack.

1. LOB access: While the BizTalk Oracle adapter uses an ODBC driver to connect to Oracle DB, the WCF-based Oracle DB adapter uses Oracle’s ODP.NET

2. Inbound processing: The BizTalk Oracle adapter supports a “Polling Query” and a “TableChangedEvent” mechanism for receiving data from Oracle into BizTalk. The WCF-based Oracle DB adapter replaces these mechanisms with a single polling based mechanism that that supports the following key features:

a. Users can generate a strongly typed schema for the inbound message from a polling statement which is typically a SELECT statement

b. Users can specify a post poll statement that can be a PL/SQL block of code that would be executed by the adapter after the polling statement

c. The polling statement and the post poll statement are executed in a transaction context

d. Users can specify the transaction isolation level required to be used for the transaction

e. The polling statement, post poll statement and the transaction isolation levels are typically specified via binding properties

3. Design-time: The WCF-based Oracle DB adapter uses a standardized GUI for browsing, searching and retrieving metadata for LOB artifacts. This GUI is accessible as part of BizTalk design time and Visual studio. The WCF-based Oracle DB adapter also provides some design-time enhancements including browsing of procedures, functions and packages including the procedures and functions constituting the package in a categorized and hierarchical manner

4. Metadata retrieval: Unlike the BizTalk adapter where metadata can only be retrieved in the form of XML schemas in BizTalk, the WCF-based Oracle DB adapter exposes programming access to retrieving metadata. This includes WCF’s standard MEX interfaces and WCF LOB adapter SDK’s special MetadataRetrievalContract that supports both browse and search operation in addition to metadata retrieval

5. Programming Interface: The WCF-based Oracle DB adapter, being a WCF custom binding element, naturally exposes WCF’s programming interfaces including the WCF service model and WCF channel model in addition to the WCF web service model (when hosted in IIS) as opposed to the BizTalk Oracle adapter that exposes XML message schemas for BizTalk users for integration

6. Transactions: The WCF-based Oracle DB adapter supports transactions in WCF channel/service model programming modes and from BizTalk. Transactions are supported in outbound (adapter to LOB) and inbound scenarios (LOB to adapter)

7. REF cursors: The WCF-based Oracle DB adapter supports Oracle IN, OUT and INOUT REF cursors including the strongly typed and weakly typed variants unlike the BizTalk Oracle adapter.

8. LOB types: The WCF-based Oracle DB adapter provides rich support for BLOB, CLOB and BFILE types in tables and stored procedures/functions. The adapter also exposes special operations for streaming BLOB/CLOB/BFILE data in the WCF service model interface.

RECORD types: Enhancing on top of ODP.NET, the WCF-based Oracle DB adapter supports RECORD type parameters in stored procedures/functions even though these types are not natively supported by ODP.NET (version