ADO.NET 2.0 Features

Configuring OraclePermission

The DemandOraclePermission configuration attribute is used to enable or disable OraclePermission demand for an ODP.NET API. The DemandOraclePermission value can be specified in the Windows registry or an individual application configuration file.

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

HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\ODP.NET\Assembly_Version\DemandOraclePermission

Here Assembly_Version is the full assembly version number of Oracle.DataAccess.dll. The DemandOraclePermission key is of type REG_SZ. It can be set to either 1 (enabled) or 0 (disabled).

You can also enable OraclePermission demand for an individual application using its application configuration file. The following example enables the DemandOraclePermission property in an application configuration file:

<configuration>
  <oracle.dataaccess.client>
    <settings>
      <add name="DemandOraclePermission" value="1"/>
    </settings>
  </oracle.dataaccess.client>
</configuration>

An application or assembly can successfully access the database if OraclePermission has been added to the permission set associated with the assembly's code group. A system administrator can modify the appropriate permission set manually or by using the Microsoft .NET configuration tool (Mscorcfg.msc).

Administrators may also use an appropriate .NET Framework Tool, such as the Code Access Security Policy Tool (Caspol.exe), to modify security policy at the machine, user, and enterprise levels for including OraclePermission.

OracleConnection makes security demands using the OraclePermission object when OraclePermission demand has been enabled using DemandOraclePermission configuration attribute. Application developers should make sure that their code has sufficient permission before using OracleConnection.

Configuring OraclePermission for Web Applications with High or Medium Trust Levels

For Web applications operating under high or medium trust, OraclePermission needs to be configured in the appropriate web_TrustLevel.config file, so that the application does not encounter any security errors.

OraclePermission can be configured using the OracProvCfg tool. OraProvCfg.exe adds appropriate entries to the web_hightrust.config and web_mediumtrust.config files associated with the specified .NET framework version.The following example illustrates using the OraProvCfg tool for configuring OraclePermission in a .NET 2.0 Web application:

OraProvCfg.exe /action:config  /product:odp /component:oraclepermission
               /frameworkversion:v2.0.50727
               /providerpath:full_path_of_Oracle.DataAccess.dll

On running the preceding command, the following entry is added to the web_hightrust.config and web_mediumtrust.config files under the ASP.NET permission set:

<IPermission class="Oracle.DataAccess.Client.OraclePermission, Oracle.DataAccess,
Version=2.112.2.0, Culture=neutral, PublicKeyToken=89b483f429c47342" version="1"
Unrestricted="true" />

OraProvCfg can also be used to remove these entries from the .config files when required. The following example illustrates this:

OraProvCfg.exe /action:unconfig  /product:odp  /component:oraclepermission
               /frameworkversion:v2.0.50727
               /providerpath:full_path_of_Oracle.DataAccess.dll

Configuring OraclePermission for Windows Applications Running in a Partial Trust Environment

For Windows applications operating in a partial trust environment, the OraclePermission entry should be specified under the appropriate permission set in the security.config file. The security.config file is available in the %windir%\Microsoft.NET\Framework\{version}\CONFIG folder.

The following example specifies the OraclePermission entry for a .NET 2.0 Windows application:

<IPermission class="Oracle.DataAccess.Client.OraclePermission, Oracle.DataAccess,
 Version=2.112.2.0, Culture=neutral, PublicKeyToken=89b483f429c47342" version="1"
 Unrestricted="true" />

User Customization of Metadata

ODP.NET provides a comprehensive set of database schema information. Developers can extend or customize the metadata that is returned by the GetSchema method on an individual application basis.

To do this, developers must create a customized metadata file and provide the file name to the application as follows:

1. Create a customized metadata file and put it in the CONFIG subdirectory where the .NET framework is installed. This is the directory that contains machine.config and the security configuration settings.

   This file must contain the entire set of schema configuration information, not just the changes. Developers provide changes that modify the behavior of the schema retrieval to user-specific requirements. For instance, a developer can filter out internal database tables and just retrieve user-specific tables

2. Add an entry in the app.config file of the application, similar to the following, to provide the name of the metadata file, in name-value pair format.

   <oracle.dataaccess.client>
     <settings>
       <add name="MetaDataXml" value="CustomMetaData.xml" />
     </settings>
   </oracle.dataaccess.client>
   

When the GetSchema method is called, ODP.NET checks the app.config file for the name of the customized metadata XML file. First, the GetSchema method searches for an entry in the file with a element named after the provider, in this example, oracle.dataaccess.client. In this XML element, the value that corresponds to the name MetaDataXml is the name of the customized XML file, in this example, CustomMetaData.xml.

If the metadata file is not in the correct directory, then the application loads the default metadata XML file, which is part of ODP.NET.

Implicit Transaction Enlistment Using TransactionScope

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);
    }
  }
}

Explicit Transaction Enlistment Using CommittableTransaction

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);
    }
  }
}

Local Transaction Support for Older Databases

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 fist 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.

Data Types Supported by Bulk Copy

The data types supported by Bulk Copy are:

• ORA_SB4

• ORA_VARNUM

• ORA_FLOAT

• ORA_CHARN

• ORA_RAW

• ORA_BFLOAT

• ORA_BDOUBLE

• ORA_IBDOUBLE

• ORA_IBFLOAT

• ORA_DATE

• ORA_TIMESTAMP

• ORA_TIMESTAMP_TZ

• ORA_TIMESTAMP_LTZ

• ORA_INTERVAL_DS

• ORA_INTERVAL_YM

Bulk copy does not support overwrites.

Restrictions on Oracle Bulk Copy of a Single Partition

• The table that contains the partition cannot have any global indexes defined on it.

• The tables that the partition is a member of cannot have referential and check constraints enabled.

• Enabled triggers are not allowed.

Integrity Constraints Affecting Oracle Bulk Copy

During a Oracle bulk copy, some integrity constraints are automatically enabled or disabled, as follows:

Enabled Constraints

During an Oracle bulk copy, the following constraints are automatically enabled by default:

• NOT NULL

• UNIQUE

• PRIMARY KEY (unique-constraints on not-null columns)

NOT NULL constraints are checked at column array build time. Any row that violates the NOT NULL constraint is rejected.

UNIQUE constraints are verified when indexes are rebuilt at the end of the load. The index is left in an Index Unusable state if it violates a UNIQUE constraint.

Disabled Constraints

During an Oracle bulk copy, the following constraints are automatically disabled by default:

• CHECK constraints

• Referential constraints (FOREIGN KEY)

If the EVALUATE CHECK_CONSTRAINTS clause is specified, then CHECK constraints are not automatically disabled. The CHECK constraints are evaluated during a direct path load and any row that violates the CHECK constraint is rejected.

Database Insert Triggers

Table insert triggers are disabled when a direct path load begins. After the rows are loaded and indexes rebuilt, any triggers that were disabled are automatically reenabled. The log file lists all triggers that were disabled for the load. There should be no errors reenabling triggers.

Unlike integrity constraints, insert triggers are not reapplied to the whole table when they are enabled. As a result, insert triggers do not fire for any rows loaded on the direct path. When using the direct path, the application must ensure that any behavior associated with insert triggers is carried out for the new rows.

Field Defaults

Default column specifications defined in the database are not available with direct path loading. Fields for which default values are desired must be specified with the DEFAULTIF clause. If a DEFAULTIF clause is not specified and the field is NULL, then a null value is inserted into the database.