Wednesday, December 26, 2012

bizTalk Oracle Adapter Limitations

Adapter Limitations

This topic has not yet been rated - Rate this topic

The following are known limitations of the Microsoft BizTalk Adapter for Oracle Database.

Array binding is not supported because it is a performance issue. According to Oracle documentation, if you include a large amount of data in a single Biztalk message to the adapter, you gain performance because there are fewer network round trips between the Oracle database and the adapter; however, the Oracle ODBC driver tries to minimize these network calls when array binding is not used.
BFile is supported as an IN parameter only.
BFile and BLOB cannot be used with the Table methods, Insert, Update, or Query.
Large object (LOB) types are supported as IN parameters only. When they appear as INOUT, RETURN and OUT, the procedure is not displayed in the browser, and therefore is not callable. LOBs are supported in tables in the Insert, Update, or Query methods.

There are two types of LOBs: a character LOB (CLOB) and a binary LOB (BLOB). The remove method on the table is available on CLOB tables because BizTalk Adapter for Oracle Database does not have to know the types of the columns in the table to remove all data.
When a table contains columns of types that are not supported by the adapter for that database system, the table only shows the Remove method.
Some PL/SQL stored procedures have a parameter of a user-defined data type for an Oracle Table. Packages that contain PL/SQL using a cursor or a ref cursor, as in some native APIs, do not generate the schema.
Records and cursors are not supported as return types as either input or output of stored procedures, as the ODBC driver does not define the metadata for these.
User-defined types (objects) are not supported for stored procedure in/inout/out/return types.
Empty strings and strings with only white space characters are treated as NULL string by BizTalk Adapter for Oracle Database. Oracle also treats a character with a length of zero as null.

Literal Number Support

Oracle treats literal numbers differently. Internally the database requires numbers to be formatted with the semi-colon instead of the period. Refer to Oracle documentation for more information about how Oracle treats literal numbers: www.cs.umb.edu/cs634/ora9idocs/server.920/a96540/sql_elements3a.htm#3411.

Operations Modes

This topic has not yet been rated - Rate this topic

You can expose tables, views, and stored procedures using the Microsoft BizTalk Adapter for Oracle Database. The adapter supports two modes of use:

Session-based mode
Connection pool-based mode

You can use a mixture of session-based and connection pool-based references to tables/views and stored procedures as needed. For example, a query from an XML-based client returns thousands of records, each of which may have dozens of fields. The XML document is large and performance might be a concern; however, most operations operate on a small set of records.

Session-Based Mode

The session-based mode is intended for use with applications such as Oracle Projects where a series of stored procedures must be executed in a certain sequence and then the work committed. Microsoft BizTalk Adapter for Oracle Database supports both commit and rollback. It does not support two-phase commit because the prepare step is implicit, and BizTalk Adapter for Oracle Database does not guarantee data integrity in the event of a system failure. A unit of work must be committed on the same connection on which it was started; you must perform these operations in the context of a session.

When you create a session, you get a dedicated connection that stays until the session ends. The session keeps the connection object. BizTalk Adapter for Oracle Database maintains the client-proxy/plus/server-object pairs.

Queries on Oracle Tables

If you call GetTable twice by using the same metadata path to an Oracle table in the same session, you get two different server objects and client proxies that point to the same Oracle table. BizTalk Adapter for Oracle Database creates the client-proxy/plus/server-object pairs. The Session ID is the same; however, the Table IDs are different, which means BizTalk Adapter for Oracle Database created or used a different reference to that table.

Updating a Table

To update an Oracle table, call insert/update/delete on a table, and then commit.

If you have two different references, you can use both references to call insert/update/delete and then query—each reference sees the table in its original state until the changes are committed. If ReferenceA commits and then ReferenceB commits, errors might occur. For example, this might occur if the second set of committed operations updated a record which the first reference deleted.

Connection Pool-Based Mode

Each call to a table/view or stored procedure is a single unit of work, and it either succeeds or fails. If the call fails, the database is rolled back. This kind of call uses pools of connections that are automatically acquired and released for each call. This guarantees there is a connection available to service the request when a request is received.

Time-outs

If a connection time-out has been configured, the ODBC driver or Oracle database might still return an error. BizTalk Adapter for Oracle Database performs stateless calls into the Oracle database. Each call obtains a connection from the pool, makes a call that was instantly committed, and then releases the connection. All server objects are transient.

BizTalk Adapter for Oracle Database can also turn off the autocommit on the ODBC connection if it is required by Oracle Database. This means you must explicitly commit or roll back all table/view and stored procedure operations. The adapter cannot control a stored procedure call that causes data to be committed internally.

Tutorial: Using the BizTalk Adapter for Oracle to Insert Data Into an Oracle Database

This topic has not yet been rated - Rate this topic

The BizTalk Adapter for Oracle can be used to update an Oracle table with information received from a trading partner or internal application. This walkthrough describes an SDK sample that illustrates this functionality.

Prerequisites

The appropriate Oracle client must be installed on your BizTalk Server. See BizTalk Adapter for Oracle Database System Requirements for more information on the supported versions of the Oracle client.
It is recommended that you install the Oracle Enterprise Manager or the Oracle SQL*Plus utility on the BizTalk Server so that you can verify the results of the walkthrough. These tools can be installed with the Oracle client.
This tutorial requires that you have an Oracle Database with the SCOTT schema installed with the table EMP. The scott.sql script in a standard Oracle Database installation that creates the SCOTT schema can be found in the <ORACLE_HOME>\rdbms\admin\ directory and can be executed using the Oracle SQL*Plus utility. For information about using the SQL*Plus utility see the Oracle documentation.

Note

The SCOTT schema is installed with the table EMP by default when you create a new Oracle database.
Visual Studio 2005 must be installed in order to build and deploy the sample.

Note
The SCOTT schema is installed with the table EMP by default when you create a new Oracle database.

What This Sample Does

This sample picks up an XML file from a folder, sends the file to an orchestration, and then uses the Oracle Adapter to execute an INSERT statement on the Oracle database to insert data from the XML file into a table.

How This Sample is Designed and Why

This sample was designed in Visual Studio 2005 and was created to illustrate basic functionality using the BizTalk Adapter for Oracle with a BizTalk orchestration.

Where to Find This Sample

The sample is located in the following folder:

\Program Files\Microsoft BizTalk Adapters for Enterprise Applications\Oracle(r) Database\Sdk\OracleDBOneWaySend

The following table shows the files in this sample and describes their purpose.

Runtime Project Filename	Runtime Project File Description
OneWaySend.btproj, OneWaySend.sln	Project and solution files for the application.
EMPService.xsd, EMPService_1.xsd, EMPService_2.xsd	Schema files for the application. Note The adapter schema files in the project were originally created using the Add Adapter Metadata Wizard. For more information on the Add Adapter Metadata Wizard see the topic "How to Add Adapter Metadata to a BizTalk Project" in the BizTalk Server 2006 documentation.
OracleDBOneWaySend.odx	The orchestration used by the application.
OracleDBOneWaySend.snk	The strong naming key file.

How to Use This Sample

Create the EMP table from the SCOTT sample schema

Use the Oracle Enterprise Manager or the Oracle SQL*Plus utility to create the EMP table on an Oracle database if this table does not already exist. The scott.sql script in a standard Oracle database installation that creates the SCOTT schema can be found in the <ORACLE_HOME>\rdbms\admin\ directory and can be executed using the Oracle SQL*Plus utility. For information about using the SQL*Plus utility see the Oracle documentation.

Create a new instance of the Oracle(r) Database adapter

Launch the BizTalk Server 2006 Administration Console. Click Start, Programs, Microsoft BizTalk Server 2006, BizTalk Server Administration.
In the BizTalk Server 2006 Administration Console, expand BizTalk Server 2006 Administration, expand BizTalk Group, expand Platform Settings, and then click Adapters.
Right-click Adapters and point to New, Adapter… to display the Adapter Properties dialog.
Enter a value for the Name field, for example OracleDb.
Select Oracle(r) Database from the list of adapters available in the Adapter dropdown and click OK.

Create a System DSN to connect to the Oracle database

Launch the ODBC Data Source Administrator. Click Start, Settings, Control Panel, Administrative Tools, Data Sources (ODBC).
In the ODBC Data Source Administrator click the System DSN tab and click the Add button.
Select the Oracle driver that was installed with the Oracle client from the list of available drivers. For example, if you installed the Oracle database 10.2.0.1 client, the name of the driver might be Oracle in Ora10201Client.
Click Finish to display the Oracle ODBC Driver Configuration dialog.
Enter a value for the Data Source Name field, this name must match the named defined as the TNS Service Name configured for the Oracle database, for example OraServer.BizTalk. For information about the TNS Service Name, see the Oracle documentation.
Enter the appropriate value for the TNS Service Name field; this entry must match the name defined for the TNS Service Name configured for the Oracle database, for example OraServer.BizTalk.
Enter the value scott into the User ID field. This user account is associated with the EMP database by default.
Click the Test Connection button to verify the connection to the database. When prompted for credentials enter the appropriate credentials for the EMP table into the Password field and click OK. A dialog box will be displayed indicating whether the test succeeded or failed. This connection must succeed in order to use the BizTalk Adapter for Oracle. If the test connection fails there should be information displayed in the dialog box that indicates the reason for the failure.

Note

The default password for the EMP table associated with the scott schema is tiger.

Note
The default password for the EMP table associated with the scott schema is tiger.

Create a BizTalk Send Port

In the BizTalk Server 2006 Administration Console, expand BizTalk Server 2006 Administration, expand BizTalk Group, expand Applications, expand BizTalk Application 1, and click Send Ports.
Right-click Send Ports and point to New, Static One-Way Send Port… to display the Send Port Properties dialog.
Enter a value for the Name field, for example OracleDBOneWaySP.
Select the Oracle database adapter from the list of available adapters in the Type dropdown box and click the Configure button to display the adapter Transport Properties dialog box.

Note

This value is the name that was specified when the Oracle database adapter was created in the BizTalk Server 2006 Administration Console.

Note
This value is the name that was specified when the Oracle database adapter was created in the BizTalk Server 2006 Administration Console.

Enter the following values for the Adapter Required Properties:

Property	Value
Password	Password associated with the scott account. By default this password is tiger.
PATH	Path to the BIN directory installed with the Oracle client, for example C:\Oracle\product\10.2.0\client_1\BIN
Service name	Name defined for the System DSN that is used to connect to the Oracle database. This should match the name defined for theTNS Service Name configured for the Oracle database, for example OraServer.BizTalk.
User name	scott

Click OK.
Select the XML Transmit pipeline from the list of pipelines available in the Send pipeline dropdown and click OK.
Right-click the send port and click Start to enlist and start the send port.

Create a file receive port

In the BizTalk Server 2006 Administration Console, expand BizTalk Server 2006 Administration, expand BizTalk Group, expand Applications, expand BizTalk Application 1, and click Receive Ports.
Right-click the Receive Ports folder and then click New, One-Way Receive Port... to display the Receive Port Properties dialog.
Enter a value for the Name field, for example OracleDBOneWayFileRP, and click OK.

Create a file receive location

Create a folder to be monitored by the file receive location, for example C:\Filesource.
Right-click the new receive port and then click New, Receive Location… to display the Receive Location Properties dialog.
Enter a value for the Name field, for example OracleDBOneWayFileRL.
Select FILE from the list of available adapters in the Type dropdown box and click the Configure button to display the adapter Transport Properties dialog box.
Enter the location of the folder that you created earlier for the Receive Folder property and click OK.
Select XMLReceive from the list of available pipelines in the Receive pipeline dropdown box and click OK.
Right-click the receive location and click Enable.

Modify the Adapter schema target namespace property

Launch Visual Studio 2005 and open OneWaySend.sln. Click File, Open, Project/Solution… to display the Open Project dialog.
Browse to the OneWaySend.sln file, click to select this file and click Open to open the solution that contains the sample project.
Click the View menu and select Solution Explorer to display the Solution Explorer.
Double-click the EMPService_1.xsd file in the Solution Explorer to open it.
Right-click the Schema node of EMPService_1.xsd and select the Properties menu option to display the properties for the schema.

Edit the Target Namespace property to use the appropriate values for the adapter name and the System DSN/TNS Service name, for example the Target Namespace property should read as follows:

http://schemas.microsoft.com/[OracleDb://OraServer.BizTalk/SCOTT/Tables/EMP]

Where OraServer.BizTalk is the name defined for the System DSN and the TNS Service Name configured for the Oracle database.

Important
If the configured value for Target Namespace does not match the namespace specified in the input document instance then a routing failure will occur when the input document instance is processed by BizTalk Server.

Generate a document instance from the Adapter schema

Right-click the Schema node of EMPService_1.xsd and select the Properties menu option to display the properties for the schema.
Select Insert from the list of available nodes in the Root Reference dropdown box. This should be done so that when you generate a sample document instance it will be generated from the Insert node of the schema.
Right-click EMPService_1.xsd in Solution Explorer and click Properties to display the Property Pages dialog.
Click to select the Output Instance Filename option under the General section of the Property Pages dialog.
Click the ellipses button (…) to display the Select Output File dialog.
Specify a folder and name for the output file instance, for example C:\instance.xml and click Save, and then click OK.

Note

Do not specify the location of the folder that was specified for the file receive location here.
Right-click EMPService_1.xsd in Solution Explorer and click Generate Instance to generate a document instance in the specified location.
Right-click the Schema node of EMPService_1.xsd and select the Properties menu option to display the properties for the schema.
Select (Default) from the list of available nodes in the Root Reference dropdown box.

Note
Do not specify the location of the folder that was specified for the file receive location here.

Modify the generated document instance

Open the generated document instance in a text editor such as Notepad and edit the contents of the document instance to more closely approximate a sample employee record. For example the record below describes an employee named Steve with the job title of Architect:

<ns0:Insert xmlns:ns0="http://schemas.microsoft.com/[OracleDb://OraServer.BizTalk/SCOTT/Tables/EMP]">
  <ns0:Rows>
    <ns0:Record>
      <ns0:EMPNO>8000</ns0:EMPNO>
      <ns0:ENAME>Steve</ns0:ENAME>
      <ns0:JOB>Architect</ns0:JOB>
      <ns0:MGR>7839</ns0:MGR>
      <ns0:HIREDATE>1999-05-31T13:20:00.000-05:00</ns0:HIREDATE>
      <ns0:SAL>5000</ns0:SAL>
      <ns0:COMM>0</ns0:COMM>
      <ns0:DEPTNO>10</ns0:DEPTNO>
    </ns0:Record>
  </ns0:Rows>
</ns0:Insert>

Note
In the example above, OraServer.BizTalk is a placeholder for the name of the System DSN as viewed in the ODBC Data Source Administrator.

Save the modified document instance.

Build and deploy the project

Right-click the OneWaySend project in Solution Explorer and click Properties to display the Property Pages dialog for the project.
Click the Deployment option under Configuration Properties in the left pane of the Property Pages dialog.
Enter the appropriate values for the Server property and the Configuration Database property under BizTalk Group in the right pane of the Property Pages dialog and click OK.
Right-click the OneWaySend project in Solution Explorer and click Deploy to build the project and deploy the assembly to the BizTalk Server configuration database.

Bind and Enlist the orchestration

In the BizTalk Server 2006 Administration Console, expand BizTalk Server 2006 Administration, expand BizTalk Group, expand Applications, expand BizTalk Application 1, and click Orchestrations.
Click the Refresh button in the MMC toolbar or press the F5 key on your keyboard to refresh the BizTalk Server Administration Console view.
Double-click the orchestration to display the Orchestration Properties dialog.
Click Bindings in the left pane of the dialog to display the Bindings options for the orchestration.
Specify the appropriate values for the binding options, for example:

Parameter Value

Host

BizTalkServerApplication

FileReceivePort

OracleDBOneWayFileRP

OracleDBSendPort

OracleDBOneWaySP
Click OK.

Parameter	Value
Host	BizTalkServerApplication
FileReceivePort	OracleDBOneWayFileRP
OracleDBSendPort	OracleDBOneWaySP

Start the orchestration

In the BizTalk Server 2006 Administration Console, right-click the orchestration and click Start to enlist and start the orchestration.

Drop a document instance into the folder monitored by the file receive location

Copy the document instance that was created earlier to the folder that the file receive location is configured to monitor.

Verify that the table is updated

Use the Oracle Enterprise Manager or the Oracle SQL*Plus utility to verify that the table is updated with the information from the file.

The following sequence of events occurs if the document instance is processed successfully:

The File adapter retrieves the file from the folder and publishes it to the MessageBox as a BizTalk message.
The orchestration subscribes to this published message so the BizTalk Messaging Engine will activate an instance of the orchestration and send the message to the orchestration instance.
The orchestration instance processes the message using the logic specified in the orchestration and publishes the message back to the MessageBox.
The Oracle send port subscribes to this published message and so the BizTalk Messaging Engine sends the message to the Oracle send port.
The send port hands the message to the BizTalk Adapter for Oracle.
The BizTalk Adapter for Oracle executes an INSERT statement to update the target table with the data in the message.

Working with BizTalk Adapter 3.0 for Oracle Database Binding Properties

0 out of 1 rated this helpful - Rate this topic

The Microsoft BizTalk Adapter 3.0 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
DataFetchSize	Buffer management	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	Buffer management	Specifies the batch size for multiple record Insert operations. The default is one. For values of InsertBatchSize greater 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.XmlReaderParsingExceptionexception 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	Buffer management	Specifies the maximum size in bytes of an Oracle long data type column. The default is 32767. The maximum value for this binding property is 2147483648.	long (System.Int64)
MetadataPooling	Buffer management	ODP.NET property. Specifies whether ODP.NET caches metadata information for executed queries. The default is true, 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	Buffer management	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	Buffer management	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)
CloseTimeout	General	The WCF connection close timeout. The default is 1 minute. Not supported.	System.TimeSpan
EnableBizTalkCompatibilityMode	General	Specifies whether the BizTalk Layered Channel Binding Element should be loaded. Set this to True to load the binding element. Otherwise, set this to False. When using the adapters from BizTalk Server, you must always set this property to True. When using the adapters from Visual Studio, you must always set this property to False.	bool (System.Boolean)
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
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 istrue; 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 UseSchemaInNamesapcebinding 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)
ConnectionLifetime	Oracle Connection Pool	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	Oracle Connection Pool	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	Oracle Connection Pool	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	Oracle Connection Pool	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	Oracle Connection Pool	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	Oracle Connection Pool	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)
PollingInterval	Polling Receive	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)
PollingRetryCount	Polling Receive	Specifies the number of times that polling is retried if an exception is encountered during the polling transaction. The default is zero, which specifies that no retry is attempted. Note If the TransactionIsolationLevel binding property is set to Serializable, you may get an Oracle exception like "ORA-08177: can't serialize access for this transaction." In such a case, you must set the PollingRetryCountbinding property to define the number of times you want the adapter to retry the same transaction.	int (System.Int32)
PollingStatement	Polling Receive	Specifies the polling statement in the form of a SQL SELECT statement. The default is null. You must specify a value for PollingStatement to enable polling. The polling statement is executed against the Oracle database at the interval specified by the PollingInterval property. Important The Oracle Database adapter executes the polling statement and the post-poll statement (if specified) inside of an Oracle transaction. It is recommended 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
PostPollStatement	Polling Receive	Specifies a PL/SQL block that is executed after the polling statement and before the /POLLINGSTMT message is sent to the consumer. The default isnull; 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
TransactionIsolationLevel	Transactions	ODP.NET property. Specifies the Oracle transaction isolation level. The default is ReadCommitted. Only ReadCommitted and Serializable are supported. For more information, about using TransactionIsolationLevelin polling scenarios, see Receiving Polling-based Data-changed Messages. Note In scenarios where there is parallel access and modification to the table being polled, you must set this property to Serializable.	Microsoft.Adapters.OracleDB.TransactionIsolationLevel
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 is false, 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 set AcceptCredentialsInUri to true if you must specify credentials in the URI. For more information, see The Oracle Database Connection URI.	bool (System.Boolean)

How Do I Set Oracle Binding Properties?

You can set the Oracle binding properties when you specify a connection to an Oracle database. For information about how to set binding properties when you:

Use the Consume Adapter Service BizTalk Project Add-in or the Add Adapter Service Reference Visual Studio Plug-in, see Connecting to the Oracle Database in Visual Studio.

Important
While using the Consume Adapter Service Add-in or the Add Adapter Service Reference Plug-in, if you do not specify a value for a binding property of type string and whose default value is null then that binding property will not be available in the binding file (an XML file) or the app.config file respectively. You must manually add the binding property and its value in the binding file or the app.config file, if required.

Important

While using the Consume Adapter Service Add-in or the Add Adapter Service Reference Plug-in, if you do not specify a value for a binding property of type string and whose default value is null then that binding property will not be available in the binding file (an XML file) or the app.config file respectively. You must manually add the binding property and its value in the binding file or the app.config file, if required.

Configure a send port or receive port (location) in a BizTalk Server solution, see Manually Configuring a Physical Port Binding to the Oracle Database Adapter.
Use the WCF channel model in a programming solution, see Creating a Channel.
Use the WCF service model in a programming solution, see Specifying a Client Binding for the Oracle Database.
Use the WCF ServiceModel Metadata Utility Tool (svcutil.exe), see Using the ServiceModel Metadata Utility Tool with the BizTalk Adapter 3.0 for Oracle Database.