Oracle 11g Release 2 and .Net Framework 4.0 and Version Checking

I recently found out that Oracle is not supporting the .net 4.0 framework with any version of the Oracle Provider for .Net prior to 11.2.0.2.  (See Oracle Data Provider for .NET (ODP) Supported Configurations [ID 726240.1] from oracle support for more details). 

Since the prior versions work (10.2, 11.1, 11.2.0.1) then I needed a way to prevent end users from using the wrong version.  I searched the net and could not find any one who was able to do this so I did some digging.  After some testing I came up with the following code.

System.Data.Common.DbProviderFactory factory =
                    System.Data.Common.DbProviderFactories.GetFactory("Oracle.DataAccess.Client");

                if (factory == null)
                    return false;

                Type t = factory.GetType();

                int majorversion = t.Assembly.GetName().Version.Major;

                // Do not allow any major versions less than 4
                if (majorversion < 4)
                    return false;

This code will use the same data provider that is registered with the .net framework version you are using.  This ensures you are not out of sink in your environment and allows you to inspect the version of the dll installed.  For this example – I am just checking that ODP is 4.0 or higher (first release of odp for .net 4). 

Oracle 11g2 Minimum required components for use with .Net

The following instructions are for setting up an oracle client with the minimum required components to be used with .Net applications. 

 

1.  Download the latest Oracle 11g2 client software from Oracle. 

Note – Be sure to determine which bit level you need if using a 64 bit operating system.  If your apps are compiled to 32 bit specifically then you must use the 32 bit oracle client.  If they are compiled to run natively – then you must use the 64 bit oracle client software.

2.  Extract the zip file to a directory

3.  Open the folder and launch the setup.exe file as an administrator

    

4.  Select the custom option in Step 1 of 8 for what type of Installation you want and click next

    

 

5. Now choose the Oracle Data Provider for .NET option and click next

    

6.  Complete the remaining steps using the default options

7.  Once complete – navigate to the following folder

     [installation path]\product\11.2.0\client_1\network

8.  Create a new folder named admin

9.  Copy an existing sqlnet.ora and tnsnames.ora file into the admin directory

10.  Test the installation with a .net application or you can use one the OracleConnectionTesting utilities provided on this blog here

Oracle 11g2 does NOT export empty tables when using exp.exe

Out of habit I have been using the older data import / export functions in lieu of using the Data Pump commands that Oracle recommends.  As of 11g2 I can no longer do this.  The exp.exe no longer exports tables that do not have an entry in DBA_SEGMENTS.  This is due to a new feature named “deferred segment creation”. 

The syntax for the new Data Pump commands are different and require some additional setup / maneuvering on the Oracle Server itself to complete.  The files when importing now must be placed in the Data Pump Directory.  This can be found by running the following query against the database server.

select owner, directory_name, directory_path from dba_directories

Note that a custom directory can also be created but is not required.  An example for this is done can be found here.

http://wiki.oracle.com/page/Data+Pump+Export+%28expdp%29+and+Data+Pump+Import%28impdp%29

Old Functions

imp.exe = Import a file

imp system/password@instance  file="D:\filenameandpath.dmp" fromuser=originaluser  TOUSER = targetuser

exp.exe = Export a file

exp username/password@instance file=”D:\exportfilename.dmp” Log=”logfilename”

New Functions

impdp.exe = Import a file

impdp system/password@instance DUMPFILE=”filename.dmp” remap_schema=ORIGNALUSER:NEWUSER LOGFILE=”LogFileName.log”

expdp.exe = Export a file

expdp system/password@instance DIRECTORY=DATA_PUMP_DIR DUMPFILE=myfilename.dmp SCHEMAS=userexportingfrom

Oracle Import / Export Slow with 11g2 and other versions

I work in an environment where schemas in a database are consistently being created / dropped / imported / exported for development purposes.  Over time the import becomes slow while the export is almost unbearable.  I did some research and testing and found a way to restore the speed the database once had when first created. 

 

The following commands must be run as a sysdba.

1. Open a command promp
2. Type connect sqlplus / nolog
3. At the prompt  – enter connect sys / as sysdba;
4. Enter Password
5. Run the following two commands

exec dbms_stats.gather_dictionary_stats;

exec dbms_stats.gather_database_stats;

 

I tested this on a single use with 300 + tables, 100 + views, and numerous triggers, etc and about 1 gig worth of data

Before > 15 minutes to export

After < 2 minutes to export

Oracle 11g2 for Windows

Oracle 11.2 for Windows has been released.  Check it here for more details.  http://www.oracle.com/technology/software/products/database/index.html.  Oracle I sure hope you got this one right as the first release (11.1) had some major issues with the .net Provider. I will post an update once I have had a chance to test the new versions.

Specified Cast is Not Valid when using Oracle with .Net Applications

When pulling in a “NUMBER” field from an Oracle Database in C# or VB you must use the Convert.To function versus the Cast function or an Invalid Cast Error will occur.  In the example below I am converting an ID field to an Int32 value.  This is due to the fact that a Number cannot be implicitly converted to an Integer.

 

The following scenario uses the cast method. Notice the cast error.

 

The following method uses the Convert.To functions for the object type.  The cast error no longer occurs. 

How to find and retrieve TNSNames.ora entries in C#

The following class will find the tnsnames file that .Net looks at and load the tnsnames entries in a list of strings and return. This is a sample of using in code setting the list being returned as the data source of a combo box. 

this.cbTNSName.DataSource = TnsNamesReader.LoadTNSNames();

using System;
using System.Collections.Generic;
using System.Text.RegularExpressions;
using System.IO;

namespace OracleConnectionTester
{
    public class TnsNamesReader
    {        
        /// <summary>
        /// Get TNS Name Entries from TNSNames.ora file
        /// </summary>
        /// <returns></returns>
        public static List<string> LoadTNSNames()
        {
            List<string> DBNamesCollection = new List<string>();
            string regPattern = @"[\n][\s]*[^\(][a-zA-Z0-9_.]+[\s]*";
            string tnsNamesOraFilePath = GetPathToTNSNamesFile();

            if (!tnsNamesOraFilePath.Equals(""))
            {
                // Verify file exists
                FileInfo tnsNamesOraFile = new FileInfo(tnsNamesOraFilePath);
                if (tnsNamesOraFile.Exists)
                {
                    if (tnsNamesOraFile.Length > 0)
                    {
                        //read tnsnames.ora file                        
                        string tnsNamesContents = File.ReadAllText(tnsNamesOraFile.FullName);
                        int numMatches = Regex.Matches(tnsNamesContents,regPattern).Count;
                        MatchCollection col = Regex.Matches(tnsNamesContents, regPattern);
                        foreach (Match match in col)
                        {
                            string m = match.ToString();
                            m = m.Trim();
                            DBNamesCollection.Add(m.ToUpper());
                        }
                    }
                }
            }
            return DBNamesCollection;
        }

        /// <summary>
        /// Gets TNSNames file path from system path
        /// </summary>
        /// <returns>TNSNames.ora file path</returns>
        private static string GetPathToTNSNamesFile()
        {
            string systemPath = Environment.GetEnvironmentVariable("Path");
            Regex reg = new Regex
                ("[a-zA-Z]:\\\\[a-zA-Z0-9\\\\]*(oracle|app)[a-zA-Z0-9_.\\\\]*(?=bin)");
            MatchCollection col = reg.Matches(systemPath);

            string subpath = "network\\ADMIN\\tnsnames.ora";
            foreach (Match match in col)
            {
                string path = match.ToString() + subpath;
                if (File.Exists(path))
                    return path;
            }
            return string.Empty;
        }
        
    }
}

Oracle Connection Tester for .Net Applications

I wrote a small utility that will allow you to test connections to an Oracle database server in a .net environment.  Many times you can setup an oracle connection from a client machine and it works when you connect with SQL Plus or tnsping.exe but when you try to utilize from .net Applications issues arise.

*Updated – added support for both 32 and 64 bit data providers.

You can download the utility here:

32 bit – for use with 32 bit Oracle Data Provider Clients

OracleConnectionTester_x86.zip

64 bit – for use with 64 bit Oracle Data Provider Clients

OracleConnectionTester_x64.zip

The zip file includes the executable and Oracle Data Access Library (10.2.0.2).

Instructions for Usage

1. Launch the application
2. Select the data provider for .Net you wish to test (Microsoft or Oracle)
3. Select the TNSNames Entry from the drop down menu
4. Enter a valid username
5. Enter a valid password
6. Click Test to test the connection

A message will appear indicating the success or failure of the test.  If the test fails – it will give the exception details.

Note that the test consists of the following

1. Opens a connection to the server
2. Runs a command (“select * from dual”)
3. Closes the connection

Developing .Net Applications with Oracle – 64 bit

When developing .Net applications for the latest Windows 64 bit operating systems (Windows Vista, Server 2008, and Windows 7) there are a few items that one must watch out for to avoid issues if using the Oracle Data Provider for .Net (ODP.NET). 

1. If you are developing your applications to run on 32 bit and 64 bit operating systems with a single build / installation then you must set your projects (Executables only) to target an x86 CPU.  This will cause the JIT Complier to compile the application as 32 bit thus any assemblies called will also be 32 bit. 
2. There are some known issues with the 11g client that you may / may not experience.  If you are experiencing strange issues where internal database errors are being thrown by 11g then you may need to revert back to a 10g2 client.   This can be very hard to pin point and running 10g2 on Vista / 2008 is painful to setup.  See below for more details. 
3. There are many versions of the OracleDataAccess.dll (ODP.NET provider dll).  Make sure you use the lowest common one for your application’s reference.  If you are forced to use 10g2 – then the 2.102.2.20 version would be your best bet. 

 

Installing the Oracle 10g2 client on Windows Vista / Server 2008 is supported – but it requires multiple steps.  The following link provides basic details for doing this – but you must pay close attention to the operating system you are using.

Oracle Data Access Components 10.2.0.2 installation instructions

In summary you must do the following

Windows Vista / Server 2008 32 bit

1. Install 10.2.0.3
2. Install 10.2.0.2 (Pay close attention to the caveat for Server 2008)

Windows Vista / Server 2008 64 bit

1. Install 10.2.0.3
2. Install 10.2.0.2 (Pay close attention to the caveat for Server 2008)
3. Apply the 10.2.0.4 patch (requires access to Metalink).  This is not the same as the 10.2.0.4 client installation.  The patch number is p6810189.

For detailed instructions on how to do this please see the following post.

Installing 10g2 on Vista / Server 2008

How to install Oracle 10g2 for use with Windows Vista and Server 2008

Installation Oracle on Windows Vista x86 and x64

Note: The following is assuming you are using the Oracle Data Provider for .Net version 10.2.0.2 or 10.2.0.2.21.

The following process takes between 45 – 75 minutes based on machine speed and available memory.

Part 1 – Install 10.2.0.3 Client Software

1. Download the 10.2.0.3.0 Oracle Client installation software

2. Extract the installation

3. In the client folder right click on setup and select “Run as Administrator”. If prompted by UAC allow access.

4. On the welcome screen click Installed Products. There should be no installed products. Click Close on the Inventory form.

5. Click Next to continue

6. Select Runtime (443MB) for the installation type and click Next

7. Change the Destination Details if desired (not required) and click Next

8. All prerequisite checks should pass. Click Next

9. Click Install to begin the installation. If prompted during the installation in regards to a Windows Firewall rule – select unblock to continue.

10. Click through any prompts received to complete the installation. No additional input is required.

11. When the installation is complete the tnsnames.ora file must be configured. This can be copied in from another area or setup using the NetManager application. The default location for this file is C:\oracle\product\10.2.0\client_1\NETWORK\ADMIN

12. To confirm the Oracle Client can reach the Database server perform the following

a. Open a command prompt (Start – Run – CMD)

b. Run the following command tnsping servicename (tnsping orcl11g)

c. A response should return with a time in milliseconds from the Oracle server confirming the connection is valid.

Part 2 – Install the 10.2.0.2 Data Provider

Note : For Windows 2008 Server you must use the 10.2.0.3 installation setup program and point to the 10.2.0.2 directory for the setup xml file to launch. Please refer to the following link for more information.

http://www.oracle.com/technology/software/tech/windows/odpnet/install10202.html

1. Obtain the 10.2.0.2.21 ODAC data provider

2. Extract the installation

3. In the client folder right click on setup and select “Run as Administrator”. If prompted by UAC allow access.

4. Click Next to continue

5. The default setting Oracle Data Access Components 10.2.0.2.21 should be selected. Click Next

6. Under Specify Home Details you MUST choose the existing Oracle home in the Name: drop down menu. Otherwise you will have two separate installations which will not work.

7. If the oracle home selection was performed successfully you should see a screen similar to below with Install Status for some items listed as new install and some as installed.

8. Click Next to continue and then Next again

9. Click Install to complete the installation

Part 3 – Patch to 10.2.0.4 if on a 64 bit (x64) system

If you are running a 64 bit operating system then the following steps must be performed.

1. Download the 10.2.0.4 Patch Set – p6810189 (Oracle MetaLink Account Required)

2. Extract the installation

3. In the Disk1 folder right click on setup and select “Run as Administrator”. If prompted by UAC allow access.

4. Click Next

5. Ensure the same Oracle Home is selected

6. Click Next

7. On the Product-Specific Prerequisites page Click the box to mark the Home incompatibilities as user verified.

8. Click Next and Next again

9. Click Install to complete the installation