The TransactionScope class provides a mechanism to write transactional applications where the applications do not need to explicitly enlist in transactions.To accomplish this, the application uses the TransactionScope object to define the transactional code. Connections created within this transactional scope will enlist in a local transaction that can be promoted to a distributed transaction.

Note that the application must call the Complete method on the TransactionScope object to commit the changes. Otherwise, the transaction is aborted by default.

// C#
 
using System;
using Oracle.DataAccess.Client;
using System.Data;
using System.Data.Common;
using System.Transactions;
 
class psfTxnScope
{
  static void Main()
  {
    int retVal = 0;
    string providerName = "Oracle.DataAccess.Client";
    string constr = 
           @"User Id=scott;Password=tiger;Data Source=oracle;enlist=true";
 
    // Get the provider factory.
    DbProviderFactory factory = DbProviderFactories.GetFactory(providerName);
 
    try
    {
      // Create a TransactionScope object, (It will start an ambient
      // transaction automatically).
      using (TransactionScope scope = new TransactionScope())
      {
        // Create first connection object.
        using (DbConnection conn1 = factory.CreateConnection())
        {
          // Set connection string and open the connection. this connection 
          // will be automatically enlisted in a promotable local transaction.
          conn1.ConnectionString = constr;
          conn1.Open();
 
          // Create a command to execute the sql statement.
          DbCommand  cmd1 = factory.CreateCommand();
          cmd1.Connection = conn1;
          cmd1.CommandText = @"insert into emp (empno, ename, job) values 
                                                     (1234, 'emp1', 'dev1')";
 
          // Execute the SQL statement to insert one row in DB.
          retVal = cmd1.ExecuteNonQuery();
          Console.WriteLine("Rows to be affected by cmd1: {0}", retVal);
 
          // Close the connection and dispose the command object.
          conn1.Close();
          conn1.Dispose();
          cmd1.Dispose();
        }
 
        // The Complete method commits the transaction. If an exception has
        // been thrown or Complete is not called then the transaction is 
        // rolled back.
        scope.Complete();
      }
    }
    catch (Exception ex)
    {
      Console.WriteLine(ex.Message);
      Console.WriteLine(ex.StackTrace);
    }
  }
}

The instantiation of the CommittableTransaction object and the EnlistTransaction method provides an explicit way to create and enlist in a transaction. Note that the application must call Commit or Rollback on the CommittableTransaction object.

// C#
 
using System;
using Oracle.DataAccess.Client;
using System.Data;
using System.Data.Common;
using System.Transactions;
 
class psfEnlistTransaction
{
  static void Main()
  {
    int retVal = 0;
    string providerName = "Oracle.DataAccess.Client";
    string constr = 
           @"User Id=scott;Password=tiger;Data Source=oracle;enlist=dynamic";
 
    // Get the provider factory.
    DbProviderFactory factory = DbProviderFactories.GetFactory(providerName);
 
    try
    {
      // Create a committable transaction object.
      CommittableTransaction cmtTx = new CommittableTransaction();
 
      // Open a connection to the DB.
      DbConnection conn1 = factory.CreateConnection();
      conn1.ConnectionString = constr;
      conn1.Open();
 
      // enlist the connection with the commitable transaction.
      conn1.EnlistTransaction(cmtTx);
 
      // Create a command to execute the sql statement.
      DbCommand cmd1 = factory.CreateCommand();
      cmd1.Connection = conn1;
      cmd1.CommandText = @"insert into emp (empno, ename, job) values 
                                                     (1234, 'emp1', 'dev1')";
 
      // Execute the SQL statement to insert one row in DB.
      retVal = cmd1.ExecuteNonQuery();
      Console.WriteLine("Rows to be affected by cmd1: {0}", retVal);
 
      // commit/rollback the transaction.
      cmtTx.Commit();   // commits the txn.
      //cmtTx.Rollback(); // rolls back the txn.
 
      // close and dispose the connection
      conn1.Close();
      conn1.Dispose();
      cmd1.Dispose();
    }
    catch (Exception ex)
    {
      Console.WriteLine(ex.Message);
      Console.WriteLine(ex.StackTrace);
    }
  }
}

If the first connection in a TransactionScope is opened to a pre-Oracle Database 11g release 1 (11.1) instance, then the connection creates a distributed transaction, by default. You can optionally have the first connection create a local transaction by using the procedure described in this section.

To create local transactions in a System.Transactions scope, either the PromotableTransaction setting in the registry, machine/Web/application configuration file, or the "Promotable Transaction" connection string attribute must be set to "local".

If "local" is specified, the first connection opened in the TransactionScope uses a local transaction. If any subsequent connections are opened within the same TransactionScope, an exception is thrown. If there are connections already opened in the TransactionScope, and an OracleConnection with "Promotable Transaction=local" attempts to open within the same TransactionScope, an exception is thrown.

If "promotable" is specified, the first and all subsequent connections opened in the same TransactionScope enlist in the same distributed transaction.

If both the registry and the connection string attribute are used and set to different values, the connection string attribute overrides the registry entry value. If neither are set, "promotable" is used. This is the default value and is equivalent to previous versions of ODP.NET which only supported distributed transactions.

The registry entry for a particular version of ODP.NET applies for all applications using that version of ODP.NET.

The OracleCommand CommandTimeout property limits how long a command is allowed to execute before terminating with an exception. This setting prevents long running commands from consuming excessive resources or from blocking other necessary operations from occurring.

The database server can be interrupted via either TCP/IP urgent data or normal TCP/IP data, called out of band (OOB) or in band data, respectively. Windows-based database servers only support in band breaks, whereas all other (predominantly UNIX-based) database servers can support OOB or in band breaks.

ODP.NET, Managed Driver uses OOB breaks by default with database servers that support it. For certain network topologies, the routers or firewalls involved in the route to the database may have been configured to drop urgent data or in band the data. If the routers or firewalls can not be changed to handle urgent data appropriately, then the ODP.NET, Managed Driver can be configured to utilize in band breaks by setting the .NET configuration parameter Disable_Oob to on.

OracleDbType enumerated values are used to explicitly specify the OracleDbType value of an OracleParameter object.

Table 3-7 lists all the OracleDbType enumeration values with a description of each enumerated value.

This section explains the inference from the System.Data.DbType, OracleDbType, and Value properties in the OracleParameter class.

In the OracleParameter class, DbType, OracleDbType, and Value properties are linked. Specifying the value of any of these properties infers the value of one or more of the other properties.

ODP.NET supports PL/SQL Associative Arrays (formerly known as PL/SQL Index-By Tables) binding.

An application can bind an OracleParameter object, as a PL/SQL Associative Array, to a PL/SQL stored procedure. The following OracleParameter properties are used for this feature:

• CollectionType

  This property must be set to OracleCollectionType.PLSQLAssociativeArray to bind a PL/SQL Associative Array.

• ArrayBindSize

  This property is ignored for the fixed-length element types (such as Int32).

  For variable-length element types (such as Varchar2), each element in the ArrayBindSize property specifies the size of the corresponding element in the Value property.

  For Output parameters, InputOutput parameters, and return values, this property must be set for variable-length variables.

  If the database server supports up to 32 KB VARCHAR2, then each ODP.NET array element can store up to 32 KB characters or binary data. If the database server supports up to 4 KB VARCHAR2, then each ODP.NET array element can store up to 4 KB characters or 2 KB binary data.

• ArrayBindStatus

  This property specifies the execution status of each element in the OracleParameter.Value property.

• Size

  This property specifies the maximum number of elements to be bound in the PL/SQL Associative Array.

• Value

  This property must be set to an array of values, null, or the DBNull.Value property.

ODP.NET supports binding parameters of PL/SQL Associative Arrays which contain the following data types.

• BINARY_FLOAT

• CHAR

• DATE

• NCHAR

• NUMBER

• NVARCHAR2

• RAW

• ROWID

• UROWID

• VARCHAR2

Using unsupported data types with associative arrays can cause an ORA-600 error.

/* Setup the tables and required PL/SQL:
 
   connect scott/tiger@oracle
   CREATE TABLE T1(COL1 number, COL2 varchar2(20));
 
   CREATE or replace PACKAGE MYPACK AS 
     TYPE AssocArrayVarchar2_t is table of VARCHAR(20) index by BINARY_INTEGER;
     PROCEDURE TestVarchar2(
       Param1 IN     AssocArrayVarchar2_t,
       Param2 IN OUT AssocArrayVarchar2_t,
       Param3    OUT AssocArrayVarchar2_t);
     END MYPACK;
/
 
   CREATE or REPLACE package body MYPACK as
     PROCEDURE TestVarchar2(
       Param1 IN     AssocArrayVarchar2_t,
       Param2 IN OUT AssocArrayVarchar2_t,
       Param3    OUT AssocArrayVarchar2_t)
     IS
     i integer;
     BEGIN
       -- copy a few elements from Param2 to Param1\n
       Param3(1) := Param2(1);
       Param3(2) := NULL;
       Param3(3) := Param2(3);
       -- copy all elements from Param1 to Param2\n
       Param2(1) := Param1(1);
       Param2(2) := Param1(2);
       Param2(3) := Param1(3);
       -- insert some values to db\n
       FOR i IN 1..3 LOOP
         insert into T1 values(i,Param2(i));
       END LOOP;
     END TestVarchar2;
   END MYPACK;
/
 */
 
// C#
 
using System;
using System.Data;
using Oracle.DataAccess.Client; 
 
class AssociativeArraySample
{
  static void Main()
  {
    OracleConnection con = new OracleConnection();
 
    con.ConnectionString = "User Id=scott;Password=tiger;Data Source=oracle";
    con.Open();
    Console.WriteLine("Connected to Oracle" + con.ServerVersion);
    
    OracleCommand cmd = new OracleCommand(
      "begin MyPack.TestVarchar2(:1, :2, :3); end;", con);
 
    OracleParameter Param1 = cmd.Parameters.Add("1", OracleDbType.Varchar2);
    OracleParameter Param2 = cmd.Parameters.Add("2", OracleDbType.Varchar2);
    OracleParameter Param3 = cmd.Parameters.Add("3", OracleDbType.Varchar2);
 
    Param1.Direction = ParameterDirection.Input;
    Param2.Direction = ParameterDirection.InputOutput;
    Param3.Direction = ParameterDirection.Output;
 
    // Specify that we are binding PL/SQL Associative Array
    Param1.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
    Param2.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
    Param3.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
 
    // Setup the values for PL/SQL Associative Array
    Param1.Value = new string[3] {
      "First Element", "Second Element ", "Third Element "
    };
    Param2.Value = new string[3] {
      "First Element", "Second Element ", "Third Element "
    };
    Param3.Value = null;
 
    // Specify the maximum number of elements in the PL/SQL Associative Array
    Param1.Size = 3;
    Param2.Size = 3;
    Param3.Size = 3;
 
    // Setup the ArrayBindSize for Param1
    Param1.ArrayBindSize = new int[3] { 13, 14, 13 };
 
    // Setup the ArrayBindStatus for Param1
    Param1.ArrayBindStatus = new OracleParameterStatus[3] {
      OracleParameterStatus.Success, OracleParameterStatus.Success, 
      OracleParameterStatus.Success};
 
    // Setup the ArrayBindSize for Param2
    Param2.ArrayBindSize = new int[3] { 20, 20, 20 };
 
    // Setup the ArrayBindSize for Param3
    Param3.ArrayBindSize = new int[3] { 20, 20, 20 };
 
    // execute the cmd
    cmd.ExecuteNonQuery();
 
    //print out the parameter's values
    Console.WriteLine("parameter values after executing the PL/SQL block");
    for (int i = 0; i < 3; i++)
      Console.WriteLine("Param2[{0}] = {1} ", i, 
        (cmd.Parameters[1].Value as Array).GetValue(i));
 
    for (int i = 0; i < 3; i++)
      Console.WriteLine("Param3[{0}] = {1} ", i, 
        (cmd.Parameters[2].Value as Array).GetValue(i));
 
    // Close and Dispose OracleConnection object
    con.Close();
    con.Dispose();
    Console.WriteLine("Disconnected");
  }         
}

The array bind feature enables applications to bind arrays of a type using the OracleParameter class. Using the array bind feature, an application can insert multiple rows into a table in a single database round-trip.

The following example inserts three rows into the Dept table with a single database round-trip. The OracleCommand ArrayBindCount property defines the number of elements of the array to use when executing the statement.

// C#
 
using System;
using System.Data;
using Oracle.DataAccess.Client; 
 
class ArrayBindSample
{
  static void Main()
  {
    OracleConnection con = new OracleConnection();
    con.ConnectionString = "User Id=scott;Password=tiger;Data Source=oracle;";
    con.Open();
    Console.WriteLine("Connected successfully");
 
    int[] myArrayDeptNo = new int[3] { 10, 20, 30 };
    OracleCommand cmd = new OracleCommand();
 
    // Set the command text on an OracleCommand object
    cmd.CommandText = "insert into dept(deptno) values (:deptno)";
    cmd.Connection = con;
 
    // Set the ArrayBindCount to indicate the number of values
    cmd.ArrayBindCount = 3;
 
    // Create a parameter for the array operations
    OracleParameter prm = new OracleParameter("deptno", OracleDbType.Int32);
 
    prm.Direction = ParameterDirection.Input;
    prm.Value = myArrayDeptNo;
 
    // Add the parameter to the parameter collection
    cmd.Parameters.Add(prm);
 
    // Execute the command
    cmd.ExecuteNonQuery();
    Console.WriteLine("Insert Completed Successfully");
 
    // Close and Dispose OracleConnection object
    con.Close();
    con.Dispose();
  }
}

Statement caching helps improve performance by eliminating the need to re-parse each SQL or PL/SQL statement before execution.

If self-tuning is enabled for an application, then ODP.NET continuously monitors application behavior in order to determine the optimum value for the statement cache size. Any statement cache size value specified in the connection string, configuration file, or registry is ignored.

When the application first initializes, it uses the default value of statement cache size. As the application executes statements, ODP.NET collects statistics that are used to self-tune the value of statement cache size. Self-tuning of statement cache size results in increased performance.

The maximum number of statements that can be cached per connection is determined by the MaxStatementCacheSize configuration attribute. The MaxStatementCacheSize value can be specified in the Windows registry or XML configuration file.

The MaxStatementCacheSize setting is useful in limiting the number of cached statements, as well as the number of open cursors. This is because a cached statement equates to a cursor being opened on the server. For this reason, you should not set MaxStatementCacheSize to a value that is greater than the database OPEN_CURSORS setting.

The following Windows registry key is used to configure the MaxStatementCacheSize configuration attribute:

HKLM\Software\Oracle\ODP.NET\version\MaxStatementCacheSize

The MaxStatementCacheSize key is of type REG_SZ. It can be set to an integer value between 0 and System.Int32.MaxValue.

The following example sets the MaxStatementCacheSize property in an ADO.NET 2.0, or above, configuration file:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <oracle.dataaccess.client>
    <settings>
      <add name="MaxStatementCacheSize" value="300"/>
    </settings>
  </oracle.dataaccess.client>
</configuration>

If self-tuning is disabled for an application, then the value of statement cache size is determined by the settings in the connection string, configuration file, or the registry. If statement cache size is not specified in any of these sources, then the default value of statement cache size is set to 0. To have ODP.NET configured with the same default settings as previous releases of ODP.NET, disable self-tuning and set the StatementCacheSize value to 10.