Key Features in BizTalk Adapter for Oracle Database
This section lists the new and deprecated features in Microsoft BizTalk Adapter for Oracle Database.
Technology-related features
| Feature | Comment |
|---|---|
| New way of connecting to the Oracle database | Apart from connecting to the Oracle database using the net service name in the tnsnames.ora file (as in the previous version of the adapter), the adapter clients can now also connect to the Oracle database directly by specifying the connection parameters, and thus eliminating the need to use a net service name or the tnsnames.ora file. Not requiring the tnsnames.ora file to connect to the Oracle database saves you from the hassle of manually updating the connection parameters (net service name) in the tnsnames.ora file on every client computer when you add or update Oracle servers in your environment. For more information, see Create a connection to the Oracle Database. |
| Support for Windows Authentication | The adapter clients can use Windows Authentication to connect to the Oracle database. Windows Authentication enables you to determine the user’s identity based on the Windows logon credentials, and thus helps you to leverage the built-in security of the Windows environment. For more information about Windows Authentication in the Oracle Database adapter, see Connect to the Oracle Database Using Windows Authentication. |
Operations-related features
| Feature | Comment |
|---|---|
| Support for specifying inline values in the Insert operation | You can use the InlineValue attribute in the Insert operation to insert computed values into tables or views in the Oracle database. This is an optional attribute and is available for all simple data records in a multiple record Insert operation. If you specify a value for this attribute, it overrides the specified value of a record. For more information about the InlineValue attribute, see Insert, Update, Delete, and Select Operations on Oracle Tables and Views. |
| Enhanced polling | The Oracle Database adapter now supports receiving "polling-based" data-changed messages by using stored procedures, functions, or packaged procedures or functions to periodically poll the Oracle database. In addition to the SELECT statement, now you can specify a stored procedure, function, or packaged procedure or function as a polling statement that the adapter executes periodically to poll the Oracle database. For more information about polling, see Support for Receiving Polling-based Data-changed Messages. |
| Support for Oracle User-Defined Types (UDTs) | The Oracle Database adapter supports performing operations on artifacts in the Oracle database that contain Oracle UDTs. For information about UDT support, see Support for Oracle User-Defined Types in Oracle Database. |
| Support for composite operations | The Oracle Database adapter enables adapter clients to perform composite operations on the Oracle database. A composite operation can include any number of the following operations, and in any order: - Operations on tables and views. - Stored procedures, functions, and procedures or functions within packages that are surfaced as operations in the adapter. For more information about composite operations, see Message Schema for Composite Operations. |
| Support for executing stored procedures in the schemas not owned by the user | The Oracle Database adapter allows you to execute stored procedures in a schema even if the current user is not the owner of the schema, provided the user has permissions on the schema in Oracle. However, if the stored procedure uses RECORD types, they must be defined in the same schema as the stored procedure. For information about executing stored procedures using the Oracle Database adapter, see Operations on Functions and Stored Procedures. |
| Support for database change notifications | Adapter clients can receive database change notifications from Oracle database based on a triggering SELECT statement. The notification is sent by Oracle database to the adapter clients as and when the result set for the SELECT statement changes. For more information about database change notifications, see Considerations for Receiving Database Change Notifications. |
| Support for synonyms | Adapter clients can perform operations on synonyms created for tables, views, stored procedures, functions and packages. For information about synonyms, and how you can use the Oracle Database adapter to perform operations on synonyms, see Operations on Synonyms in Oracle Database. |
| Support for boolean parameters and PL/SQL table types | Adapter clients can perform operations in stored procedures and functions that contain boolean parameters and PL/SQL table type. |
Other Features
| Feature | Comment |
|---|---|
| New way of using the adapter in BizTalk Server | The Oracle Database adapter can be used in BizTalk either as a WCF-Custom port or a WCF-OracleDB port. If you want to use the Oracle Database adapter through a WCF-Custom port, you do not need to add the WCF-Custom port to the BizTalk Server Administration console because the WCF-Custom port is added to the BizTalk Server Administration console by default. However, if you want to use the Oracle Database adapter through a WCF-OracleDB port, you must first add the WCF-OracleDB adapter to the BizTalk Server Administration console. For more information, see Adding the Oracle Database Adapter to BizTalk Server Administration Console. |
Deprecated Features in the Oracle Adapter
The following table lists the feature that are deprecated in the current version of the Oracle Database adapter.
| Feature | Comment |
|---|---|
| Binding properties | The PollingRetryCount, TransactionIsolationLevel, and LongDataTypeColumnSize binding properties are deprecated. NOTE To set the transaction isolation level for the inbound operations, you must set appropriate value by adding the service behavior while configuring the receive port. For instructions on how to set the transaction isolation level, see Configure Transaction Isolation Level and Transaction Timeout. |
Changes to note
General
For parameters of type IN OUT REF CURSOR
If there was no change to the REF CURSOR value inside the stored procedure, the value of the output is the same as the value in the input REF CURSOR.
The input and output data in the REF CURSOR is of the same type.
Incorrect behavior of the “nil” attribute: For all simple data types, if you set the value of the nil attribute to “true”, and a value for the field or parameter is present then the Oracle database adapter incorrectly passes the specified value instead of NULL. As a workaround, if you want to pass NULL value for a field or parameter, you must ensure that no value for the field or parameter is specified. For example, to pass NULL value for a field called “name”:
<name xsi:nil="true"/>The Real, Float, and Long data types, and extra zeroes (0) at the end of the value in the result set of the Select operation are not truncated . Moreover, the result set of the Select operation always returns a value with precision 8 for the Real, Float, and Long data types.
Handling of data for the RECORD types: The value passed for these nodes depend on the value of the SkipNilNodes binding property. For more information about this binding property, see Configure the binding properties for Oracle Database.
Outbound operations: No value is sent for the parameters that do not have a value specified in the input XML file. If a default value is specified in the stored procedure, the Oracle database uses that value because no value was sent by the adapter. If a NULL value needs to be sent, the user needs to specify a NULL node in the input XML file by setting the value of “nil” attribute to “true.”
Command timeout is supported.
The UpdateLOB operation must be performed as part of a transaction. To ensure this, the value of the UseAmbientTransaction binding property must be set to True.
BizTalk Scenario
Outbound operations: If the UseAmbientTransaction binding property is “True,” the operations on the Oracle database and on the BizTalk MessageBox database are performed within the same distributed transaction. For more information about transactions in the Oracle Database adapter, see Handle Transactions with the Oracle Database adapter.
Inbound operations: You cannot use a Request-Response receive port in BizTalk Server for the inbound operations using the Oracle Database adapter. Only One-Way receive ports can be used.
Other Scenarios
Outbound operations: The adapter does not initiate a transaction. If the user wants multiple rows to be inserted within the same transaction, it is the user’s responsibility to execute the operation within a System.Transactions Transaction Scope. The user also needs to set the value of the UseAmbientTransaction property to True. For more information about transactions in the Oracle Database adapter, see Handle Transactions with the Oracle Database adapter.
Outbound operations: Sll operations performed on the same IRequestChannel/proxy object might not be performed on the same physical connection to the Oracle Database.
WCF Channel Model: The Oracle Database adapter does not support IReplyChannel while using the WCF Channel model. However, you can use IInputChannel for performing inbound operations. Further, with regards to transactions, the adapter relies on the WCF Dispatcher initiated transaction to execute the polling statement and post poll statement against the Oracle database. The transaction isolation level and the time out of the WCF Dispatcher initiated transaction can be controlled by setting appropriate values in the ServiceBehavior.