Working with BizTalk Adapter for Oracle Database Binding Properties

This topic has not yet been rated - Rate this topic

The Microsoft BizTalk Adapter for Oracle Database surfaces several binding properties. By setting these properties, you can control some of the adapter's behavior. This section describes the Oracle Database adapter binding properties. It also shows how you can access them by using .NET programming or by setting properties on a BizTalk Server physical port binding.

The Adapter Binding Properties

---

The following table shows the Oracle Database adapter binding properties grouped by category. The category refers to the node under which each binding property appears in the dialog boxes that are presented by different applications to configure the adapter (or binding).

Binding Property	Category	Description	.NET Type
CloseTimeout	General	The WCF connection close timeout. The default is 1 minute. Not supported.	System.TimeSpan
EnableBizTalkCompatibilityMode	General	Set the value of this binding property to True when using the adapter with BizTalk Server. Otherwise, you must set the value of this binding property to False.	bool (System.Boolean)
InboundOperationType	General	Specifies whether you want to perform Polling or Notification inbound operation. Default is Polling. For more information about Polling see Support for Receiving Polling-based Data-changed Messages. For more information about Notification, see Receiving Database Change Notifications.	enum
Name	General	A read-only value that returns the name of the file generated by the Add Adapter Service Reference Visual Studio Plug-in to hold the WCF client class. The Add Adapter Service Reference Plug-in forms the file name by appending "Client" to the value of the Name property. The value returned is "OracleDBBinding"; for this value, the generated file will be named "OracleDBBindingClient".	string
OpenTimeout	General	ODP.NET property. Specifies the WCF connection open timeout. The default is 1 minute. This property is implemented by using ODP.NET. Important The Oracle Database adapter always uses OpenTimeout to set the connection open timeout when it opens a connection to the Oracle database. The adapter ignores any timeout (System.TimeSpan) parameters passed when you open a communication object, such as a channel.	System.TimeSpan
ReceiveTimeout	General	Specifies the WCF message receive timeout. Essentially, this means the maximum amount of time the adapter waits for an inbound message. The default is 10 minutes. Important For inbound operations such as polling, we recommend setting the timeout to the maximum possible value, which is 24.20:31:23.6470000 (24 days). When using the adapter with BizTalk Server, setting the timeout to a large value does not impact the functionality of the adapter.	System.TimeSpan
SendTimeout	General	ODP.NET property. Specifies the WCF message send timeout. The default is 1 minute. Not supported.	System.TimeSpan
DataFetchSize	BufferManagement	ODP.NET property. Specifies the amount of data in bytes that ODP.NET fetches from the result set in one server roundtrip. The default is 65536. This property is used for performance tuning.	long (System.Int64)
InsertBatchSize	BufferManagement	Specifies the batch size for multiple record Insert operations. The default is one. For values of InsertBatchSizegreater than one, the Oracle Database adapter batches the specified number of records into a single ODP.NET call. If the number of records in the Insert operation is not a multiple of the batch size, the final batch will contain fewer records than the batch size value. For example, if the insert message has 10 records and theInsertBatchSize is set to 1, the adapter reads individual records and writes them into the Oracle database. So, the adapter performs 10 separate operations on the Oracle database. Similarly, if the insert message has 10 records and the InsertBatchSize is set to 5, the adapter will read and write 5 records at a time into the Oracle database, therefore performing only 2 insert operations. If the structure of the records is not the same across a batch, aMicrosoft.ServiceModel.Channels.Common.XmlReaderParsingException exception is thrown and the transaction is rolled back for the entire insert operation. A well-chosen value for InsertBatchSize can greatly improve adapter performance for multiple record Insert operations.	int (System.Int32)
LongDatatypeColumnSize	BufferManagement	Specifies the maximum size in bytes (32512) of an Oracle long data type column. The default is 0. You must use the default value if you are not performing operation on long data type. To prefetch the data, you must specify-1 as the value for this binding property. You must explicitly set an appropriate value for this binding property if you are: Executing a stored procedure that contains parameters of long data type. Performing a Select operation on a table that contains columns with long data type, and the SELECT statement does not include the primary key column. Note This binding property is deprecated.	long (System.Int64)
MaxOutputAssociativeArrayElements	BufferManagement	Specifies the size of the associate array that the adapter creates when performing operations that return an associative array in the response. The adapter communicates the size of the array to ODP.NET, which in turn creates a buffer depending on the array size. Default is 32. This binding property is useful when performing operations involving PL/SQL table types.	int (System.Int32)
MetadataPooling	BufferManagement	ODP.NET property. Specifies whether ODP.NET caches metadata information for executed queries. The default isTrue, which enables metadata pooling. Caching this information improves performance; however, if changes to the underlying Oracle artifacts occur on the Oracle system, this pooled metadata will be out of sync. This might cause operations performed on the Oracle system to return unexpected exceptions. This property is used for performance tuning.	bool (System.Boolean)
StatementCachePurge	BufferManagement	ODP.NET property. Specifies whether the ODP.NET statement cache associated with a connection is purged when the connection is returned to the connection pool. The default is False, which disables statement cache purging. This property is used for performance tuning.	bool (System.Boolean)
StatementCacheSize	BufferManagement	ODP.NET property. Specifies the maximum number of statements that can be cached by each ODP.NET connection. Setting this property to a non-zero value enables statement caching for connections. The default is 10. This property is used for performance tuning.	int (System.Int32)
EnablePerformanceCounters	Diagnostics	Specifies whether to enable the WCF LOB Adapter SDK performance counters and the Oracle Database adapter LOB Latency performance counter. The default is False; performance counters are disabled. The LOB Latency performance counter measures the total time spent by the Oracle Database adapter in making calls to the Oracle database.	bool (System.Boolean)
EnableSafeTyping	Metadata	Enables or disables safe typing. The default is False; safe typing is disabled. This feature controls how the adapter surfaces certain Oracle data types. For more information about safe typing, see Basic Oracle Data Types.	bool (System.Boolean)
UseSchemaInNameSpace	Metadata	Specifies whether the schema name (SCOTT, HR, and so on) is included in the xml namespace for operations and their associated types. The default is True; the schema name is included in the namespace. The advantage of not having scheme name included in the namespace is that if there is a table with same name (for example, EMP) in two different schemas then the same XML can be used to perform the simple SQL operations (Insert, Update, Delete, Select) on both tables. For example, if the UseSchemaInNamespace property is true, the namespace for these operations on the SCOTT.EMP table is "http://Microsoft.LobServices.OracleDB/2007/03/SCOTT/Table/EMP"; if it is false, the namespace is "http://Microsoft.LobServices.OracleDB/2007/03/Table/EMP". Important The message action is not affected by the UseSchemaInNamesapce binding property; it always includes the schema name. Important We strongly recommend setting this binding property to True while generating metadata. If you set this property to false, the Oracle schema names (for example, SCOTT) will not be available in the XML namespace of the generated schema. So, if there are two tables with the same name in two different Oracle schemas, and they are added to the same BizTalk project, the BizTalk project will fail to build and deploy. If you want to include such schemas in the same BizTalk project, you must manually edit them to include the Oracle schema name in the XML namespace.	bool (System.Boolean)
NotificationPort	Notification	Specifies the port number that ODP.NET must open to listen for database change notification from Oracle database. Default is -1, which signifies that ODP.NET uses a valid, random, unused port number. Important Adapter clients will not receive database change notifications if Windows Firewall is turned on. Also, turning off Windows Firewall to receive notifications is not advisable. So, to receive notifications without compromising the security of the client-side computers, we recommend specifying a positive integer value as a port number and then adding that port number to the Windows Firewall exceptions list. If you set this binding property to the default value of -1, ODP.NET uses a random port and adapter clients will not know which port to add to Windows Firewall exceptions list. For instructions on how to add ports to Windows Firewall exceptions list, see http://go.microsoft.com/fwlink/?LinkId=124685. Caution If there is more than one application in an application domain receiving notifications using the Oracle Database adapter, the NotificationPort binding property for all applications must be set to the same port number. This is because ODP.NET creates only one listener that listens on one port within an application domain.	int (System.Int32)
NotificationStatement	Notification	Specifies the SELECT statement used to register for getting notifications from Oracle database. An example SELECT statement could resemble the following. SELECT TID,ACCOUNT,PROCESSED FROM SCOTT.ACCOUNTACTIVITY WHERE PROCESSED = ‘n’ Note You must specify the database object name along with the schema name. For example,SCOTT.ACCOUNTACTIVITY. The adapter gets a notification message from Oracle database only when the result set for the specified SELECT statement changes.	string
NotifyOnListenerStart	Notification	Specifies whether the adapter sends a notification message to the adapter clients, informing that the receive location is running, when the listener starts. Default is True.	bool (System.Boolean)
ConnectionLifetime	OracleConnectionPool	ODP.NET property. Specifies the maximum duration in seconds of a connection. The default is 0. This property is used for performance tuning.	int (System.Int32)
DecrPoolSize	OracleConnectionPool	ODP.NET property. Specifies the number of connections that are closed when an excessive amount of established connections are not in use. The default is 1. This is used for performance tuning.	int (System.Int32)
IncrPoolSize	OracleConnectionPool	ODP.NET property. Specifies the number of new connections to be created when a new connection is requested and there are no available connections in the ODP.NET connection pool. The default is 5. This property is used for performance tuning.	int (System.Int32)
MaxPoolSize	OracleConnectionPool	ODP.NET property. Specifies the maximum number of connections in an ODP.NET connection pool. The default is 100. This property is used for performance tuning. Important You must set MaxPoolSize judiciously. It is possible to exhaust the number of connections available from ODP.NET, if this value is set too large.	int (System.Int32)
MinPoolSize	OracleConnectionPool	ODP.NET property. Specifies the minimum number of connections in an ODP.NET connection pool. The default is 1. This property is used for performance tuning.	int (System.Int32)
UseOracleConnectionPool	OracleConnectionPool	ODP.NET property. Specifies whether to use the ODP.NET connection pool. The default is True, which enables connection pooling. The Oracle Database adapter implements connection pooling by using the ODP.NET connection pool.	bool (System.Boolean)
PolledDataAvailableStatement	PollingReceive	Specifies the SELECT statement executed to determine whether any data is available for polling for a specific table. The specified statement must return a result set consisting of rows and columns. The value in the first cell of the result set indicates whether the adapter executes the value specified for the PollingStatement binding property. If the first cell of the result contains a positive value, the adapter executes the polling statement. For example, a valid statement for this binding property will be: Select * from <table_name> The default value of this binding property is set to: SELECT 1 FROM DUAL This implies that the adapter must continue polling irrespective of whether the table being polled has data or not. Note You must not specify stored procedures for this binding property. Also, this statement must not modify the underlying Oracle database.	string
PollingAction	PollingReceive	Specifies the action for the polling operation. You can determine the polling action for a specific operation from the metadata you generate for the operation using the Consume Adapter Service Add-in.	string
PollingInterval	PollingReceive	Specifies the transacted polling interval, that is, the interval in seconds at which the Oracle Database adapter executes the polling statement against the Oracle database. The default is 500. The polling interval is used by the adapter for the following: The time interval between successive polls. This interval is used to run the poll and post-poll queries. If these queries are executed within the specified interval, the adapter sleeps for the remaining time in the interval. The polling transaction timeout value. This value must be set large enough to include the polling statement execution time, the post-poll statement (if specified) execution time, and the time to receive the reply from the client application to commit the transaction. If the client application sends a reply before the polling interval expires, the adapter commits the transaction and waits until the polling interval is reached to execute the next poll. If the client application returns a fault, the adapter terminates the transaction. If the polling interval expires before the client application sends the reply, the transaction will time out. For more information about how to use binding properties in a polling scenario, see Receiving Polling-based Data-changed Messages.	int (System.Int32)
PollingStatement	PollingReceive	Specifies the polling statement. You can specify a simple SELECT statement or a stored procedure, function, or a packaged procedure or function for polling. If you want to poll a table or view, you must specify a SELECT query in this binding property. If you want to poll using a stored procedure, function, or procedure or function within a package, you must specify the entire request message for the respective operation in this binding property. The polling statement is executed only if the statement executed by the PolledDataAvailableStatement binding property returns some data. Important The Oracle Database adapter executes the polling statement and the post-poll statement (if specified) inside of an Oracle transaction. If you are using a SELECT statement in the PollingStatement binding property, we recommend that you specify a FOR UPDATE clause in your SELECT statement. This will ensure that the selected records are locked during the transaction and that the post-poll statement can perform any required updates on the selected records. For more information about how to use binding properties in a polling scenario, including the use of the FOR UPDATE clause; see Receiving Polling-based Data-changed Messages.	string
PollWhileDataFound	PollingReceive	Specifies whether the Oracle Database adapter ignores the polling interval and continuously polls the Oracle database, if data is available in the table being polled. If no data is available in the table, the adapter reverts to execute the SQL statement at the specified polling interval. Default is False. Consider a scenario where the polling interval is set to 60 seconds, and the statement specified for PolledDataAvailableStatement returns that data is available for polling. The adapter then executes the statement specified for the PollingInput binding property. Assuming that the adapter takes just 10 seconds to execute the statement, it will now have to wait for 50 seconds before executing the PolledDataAvailableStatement again, and then subsequently execute the polling statement. Instead, to optimize the performance you can set the PollWhileDataFound binding property to true so that the adapter can start executing the next polling cycle as soon as the previous polling cycle ends. Note This binding property is applicable both for polling on tables and views and polling using stored procedures, functions, or packaged procedures or functions.	string
PostPollStatement	PollingReceive	Specifies a PL/SQL block that is executed after the polling statement and before the /POLLINGSTMT message is sent to the consumer. The default is null; no post-poll statement is executed. The post-poll statement executes inside the polling transaction. Two common uses for the post-poll statement are to: Update a column in the rows returned in the polling statement to indicate that they have been processed and should be excluded from subsequent polling queries. Move processed records to a different table. Important If a post-poll statement is specified, PollingInterval should be set large enough for the PL/SQL block to complete before the interval expires. For more information about how to use binding properties in a polling scenario, see Receiving Polling-based Data-changed Messages.	string
SkipNilNodes	Run Time Behavior	Specifies whether the Oracle Database adapter will skip inserting or updating values for nodes that are marked as ‘nil’ in the request XML. This binding property is applicable for inserting or updating records in a table and for RECORD type parameters in stored procedures. Default is True, which means the adapter will skip passing values for nodes that are marked as ‘nil’. In this case, the default value in Oracle (if specified) is taken into account for nodes that are marked as ‘nil’. If set to False, the adapter explicitly passes a null value for these nodes. Note For nodes that are not present in the request XML, the adapter always skips passing values, irrespective of the value of the SkipNilNodes binding property. For PL/SQL tables of RECORDS, the adapter always passes a null value for nodes that are either marked as ‘nil’ or not present in the request XML, irrespective of the value of the SkipNilNodes binding property. The following example explains the difference in the adapter configuration based on the value you set for this binding property. Assume a request XML resembles the following: <EMPNO>1000</EMPNO> <ENAME>John</ENAME> <SAL nil=’true’></SAL> If SkipNilNodes is set to True, the adapter executes the following command: INSERT INTO EMP (EMPNO, ENAME) VALUES (1000, “John”); If SkipNilNodes is set to False, the adapter executes the following query: INSERT INTO EMP (EMPNO, ENAME, SAL) VALUES (1000, “John”, null); Note that in the second statement, the adapter explicitly inserts a null value for the parameter “SAL”.	bool (System.Boolean)
UseAmbientTransaction	Transactions	Specifies whether the Oracle Database adapter performs the operations using the transaction context provided by the caller. The default value is True, which means that the adapter always performs the operations in a transaction context, assuming that the client is providing the transactional context. If there are other resources participating in the transaction, the connections created enlist in System.Transaction and are elevated to an MSDTC transaction. However, there can be scenarios where you do not want the adapter to perform operations in a transactional context. For example: While performing a simple SELECT operation on the Oracle database (on a send port). While specify a polling statement that performs a SELECT operation and does not involve any changes to the table either through a DELETE statement or by invoking a stored procedure (on a receive port). Both these operations do not make any updates to the database table and hence, elevating these operations to use an MSDTC transaction can be a performance overhead. In such scenarios, you can set the binding property to false so that the Oracle Database adapter does not perform the operations in a transaction context. Note Not performing operations in a transactional context is advisable only for operations that do not make changes to the database. For operations that update data in the database, we recommend setting the binding property to true otherwise you might either experience message loss or duplicate messages depending on whether you are performing inbound or outbound operations.	bool (System.Boolean)
GeneratedUserTypesAssemblyFilePath	UDT .NET Type Generation – Design Time	Specifies the name and path of the DLL that the adapter generates, while generating metadata, containing all UDTs that are used in the metadata. You must specify a DLL name if you are generating metadata for packages, stored procedures, or functions that use UDTs. Specifying the DLL name is optional for tables and views that have UDTs. The generated DLL is saved to the same location as the executable. This binding property is required only while generating metadata. Note You must specify only one filename. For all the UDTs in the metadata, the adapter generates a single file with the given name. If you do not specify a name, the adapter generates the DLL with a GUID name. This binding property is not available in BizTalk Server while configuring a WCF-OracleDB receive or send port.	string
GeneratedUserTypesAssemblyKeyFilePath	UDT .NET Type Generation – Design Time	Specifies the name and path of the key file that the adapter uses to create a strongly-typed assembly. This binding property is optional and is required only while generating metadata. Note This binding property is not available in BizTalk Server while configuring a WCF-OracleDB receive or send port.	string
UserAssembliesLoadPath	UDT .NET Type Generation – Run Time	Specifies the name of the DLLs, separated by a semi-colon, which the adapter creates while generating metadata. These DLLs are saved at the location you specified for the GeneratedUserTypesAssemblyFilePathbinding property while generating metadata. You must manually copy these DLLs to the following locations: For BizTalk projects: Copy the DLLs at the same location as BTSNTSvc.exe. For BizTalk Server 2006 R2, this is available typically under <installation drive>:\Program Files\Microsoft BizTalk Server 2006. For BizTalk Server 2009, this is available typically under <installation drive>:\Program Files\Microsoft BizTalk Server 2009. For .NET Projects: Copy the DLLs to the \bin\Development folder within your .NET project folder. This binding property is required only while sending and receiving messages to perform operations on the Oracle database.	string
AcceptCredentialsInUri	Not surfaced by the Consume Adapter Service Add-in or the Add Adapter Service Reference Plug-in.	Specifies whether the Oracle connection URI can contain user credentials for the Oracle database. The default isFalse, which disables user credentials in the connection URI. If AcceptCredentialsInUri is False and the Oracle connection URI contains user credentials, the Oracle Database adapter throws an exception. You can setAcceptCredentialsInUri to True if you must specify credentials in the URI. For more information, see The Oracle Database Connection URI.	bool (System.Boolean)