2.7 Configuring Oracle Data Provider for .NET

The settings for specific versions of ODP.NET, can be configured in several ways for specific effects on precedence:

  • The Windows registry entries are machine-wide settings for a particular version of ODP.NET.

    Windows registry based configuration is not supported for ODP.NET, Managed Driver.

  • The machine.config settings are .NET framework-wide settings that override the Windows registry values.

  • The application or web config file settings are application-specific settings that override the machine.config settings and the Windows registry settings.


    There is one exception to app/web/config settings overriding machine.config. For oracle.manageddataaccess.client and oracle.unmanageddataaccess.client sections, a machine.config with a specific ODP.NET version subsection, that is, <version number="">, will override an app/web.config subsection that references all versions generically, that is, <version number="*">. To override the machine.config subsection, create a subsection for that version in the app/web/config file, that is, <version number="">.

  • Any attribute settings that are equivalent to the connection string override everything.

The application or web config file can be useful and sometimes essential in scenarios where more than one application on a computer use the same version of ODP.NET, but each application needs a different ODP.NET configuration. The Windows registry value settings for a given version of ODP.NET affect all the applications that use that version of ODP.NET. However, having ODP.NET configuration values in the application or web config file assure that these settings are applied only for that application, thus providing more granularities.

For example, if the application or web.config file has a StatementCacheSize configuration setting of 100, this application-specific setting forces the version of ODP.NET that is loaded by that application to use 100 for the StatementCacheSize and overrides any setting in the machine.config and in the registry. Note that for any setting that does not exist in a config file (machine.config or application/web config), the value in the registry for a loaded version of ODP.NET is used, as in previous releases.

Note that ODP.NET reads the machine.config files from the version of the .NET Framework on which ODP.NET runs, not from the version of ODP.NET.

ODP.NET only reads the Windows Registry and the XML configuration file when it is loaded into memory, thus any configuration changes made after that are not read or used until the application is re-started.

All boolean attributes in ODP.NET .NET configuration settings accept true, false, 1, and 0 as valid values. 1 is equivalent to true and 0 is equivalent to false.

2.7.1 Oracle Client Configuration File Automated Setup During Installation

When installing Oracle Data Access Components (ODAC) in a new Oracle Home, Oracle Universal Installer (OUI) automatically copies the Oracle local naming (tnsnames.ora), profile (sqlnet.ora), and directory (ldap.ora) parameter files and settings from an existing Oracle home into the newly installed ODAC home, as long as they share the same bitness. That is, they are both 32-bit installations or they are both 64-bit installations.

Alternatively, existing *.ora files can be copied over from another existing Oracle home, besides the last active one, to the new ODAC Oracle home. OUI provides location information for these files from up to three other existing Oracle homes if they exist. The *.ora files can be customized if the new Oracle home uses a different configuration from the previous Oracle home from which the files were copied over.

If you install into an existing ODAC or RDBMS Oracle home, then no new *.ora files is copied or created.

If you install onto a computer without any previous Oracle homes present, then OUI prompts the user for the database connection alias information. OUI then automatically creates the tnsnames.ora file. If no alias information is provided, then no tnsnames.ora file is created. Even if the user does not have all the database connection information readily available, Oracle recommends inserting placeholder values during the install process, then modifying the tnsnames.ora file later with actual values to replace the placeholders.

2.7.2 Oracle Client Configuration File Settings

ODP.NET tnsnames.ora, sqlnet.ora, and ldap.ora parameter values can be set in a .NET configuration file or within the *.ora file itself. The *.ora file location can be a location different from the standard ORACLE_HOME/network/admin directory. The *.ora settings order of precedence is similar to ODP.NET's settings order of precedence. The main difference is that the *.ora files themselves are included in the search order. The tnsnames.ora and sqlnet.ora precedence order is as follows:

  1. app.config or web.config

  2. machine.config

  3. File location specified by TNS_ADMIN setting

  4. The current .EXE or web application root directory

  5. %ORACLE_HOME%\network\admin if using ODP.NET, Unmanaged Driver

The ldap.ora precedence order is as follows:

  1. app.config or web.config

  2. machine.config

  3. File location specified by TNS_ADMIN setting in .NET config file

  4. File location specified by LDAP_ADMIN setting in .NET config file

  5. The current .EXE or web application root directory

  6. %ORACLE_HOME%\network\admin if using ODP.NET, Unmanaged Driver

  7. %ORACLE_HOME%\ldap\admin if using ODP.NET, Unmanaged Driver

Oracle recommends using an app.config or web.config file to store all these Oracle Client configuration parameter settings.

Once the first tnsnames.ora, sqlnet.ora, and ldap.ora are found and read, no additional *.ora file lower in the precedence order is read. That means all Oracle Client configuration settings must be made in the app.config, web.config, machine.config, or the first set of *.ora files found. Additional parameter values set in *.ora files lower in the precedence order will not be read.

2.7.3 Machine-Wide Configuration Option

ODAC OUI and xcopy installs ODP.NET with either machine-wide or non-machine-wide configuration for managed and unmanaged ODP.NET. Machine-wide configuration makes global changes to the machine's .NET setup, including placing the provider assembly into the Global Assembly Cache (GAC) and updating the machine.config with configuration section handler and DbProviderFactory information.

Machine-wide configuration also creates a TNS_ADMIN machine.config setting. If TNS_ADMIN already exists as a Windows environment variable in an OUI ODAC installation, then the TNS_ADMIN machine.config setting is set to that directory location. If TNS_ADMIN does not already exist for an OUI ODAC installation, then the machine.config TNS_ADMIN value is set to ORACLE_HOME\network\admin. Xcopy installations always create a machine.config TNS_ADMIN value set to ORACLE_HOME\network\admin.

For ODAC OUI machine-wide configuration installations only, the LDAP_ADMIN setting may also be created in machine.config if an ldap.ora file can be found through the existing LDAP_ADMIN or TNS_ADMIN Windows environment variables. ODAC OUI installations may also create a NAMES.DIRECTORY_PATH setting in machine.config for machine-wide configuration.

If non-machine-wide configuration is selected, then none of these changes are made. Starting with release 12.2, ODAC installs default to non-machine-wide configuration for a new Oracle home installation. For existing Oracle homes, ODAC re-installs the default to the same configuration setting chosen for that Oracle home from the previous installation. In non-ODAC installations, such as the standard Oracle Database or Client installation, only non-machine-wide installation is available.

If you plan to install ODAC and the ODP.NET NuGet install on the same machine, then ODP.NET should be configured for non-machine-wide, especially if both share the same ODP.NET version number that .NET Framework uses to distinguish assembly versions, for example,

Users can reconfigure ODP.NET from machine-wide configuration to non-machine-wide configuration by re-installing ODP.NET to the same Oracle home where ODP.NET of the same version is already installed. For example, if you have already configured ODP.NET machine-wide, then you can re-configure it by re-installing ODP.NET onto the same Oracle home and selecting the non-machine-wide configuration option.

For applications that depend on an ODP.NET version that was not configured machine-wide, it is important to note the following:

  • ODP.NET assembly or assemblies that the application depends on will need to be copied over to the application directory.

  • Proper .NET configuration settings will be required to use Provider Factory, or Provider-specific configuration, or both.