Verify Oracle Client Installation

• Download source - 14.4 KB
• Download ConnectionTester - 29.4 KB

Introduction

Today, one has several options to establish a connection to an Oracle database, for example ODBC, OLE DB, ODP.NET or even third party drivers. This small tool will verify (almost) all of them.

Background

When you develop an application which connects to an Oracle database, you have to decide on a technology to use for connection. Sometimes, your customer have got already an Oracle client installed and are not able or willing to change that. When you ask "which component did you install?" many times, you do not get a clear answer, but only "We have an Oracle client installed on our machines." The ConnectionTester.exe tests installed components.

Another use-case is when you have several components and/or Oracle versions installed on your PC and you like to test all variants of it.

ConnectionTester.exe tests the following providers:

• +|-ODP: Test (+) or skip (-) Oracle Data Provider for .NET
• +|-ADO: Test (+) or skip (-) Microsoft .NET Framework Data Provider for Oracle
• +|-OleDB: Test (+) or skip (-) OLE DB (Microsoft provider and Oracle provider)
• +|-ODBC: Test (+) or skip (-) ODBC drivers (Microsoft driver and Oracle driver)
• +|-DevArt: Test (+) or skip (-) Devart dotConnect
• +|-all: Test all possible Oracle providers/drivers, evaluated before other switches


• wait: Wait for key stroke after each connetion
• cs: Print ConnectionString for each connection (Consider security, password is shown as clear text)

Switches are not case-sensitive

Using the Application

At the first step, the command line parameters are evaluated. Then the program simply creates a DbConnection object, opens a connection and closes again. Based on input parameter, it checks all providers one by one.

Syntax is similar to SQL*Plus, all the following commands are valid:

c:\> ConnectionTester.exe scott/tiger@DB1

c:\> ConnectionTester.exe scott@DB1
Password: *****

c:\> ConnectionTester.exe
Database or <ENTER> for exit: DB1
User: scott
Password: *****

You have two versions of ConnectionTester, ConnectionTester_x86.exe and ConnectionTester_x64.exe. Obviously, ConnectionTester_x86.exe runs in 32-bit mode, whereas ConnectionTester_x64.exe runs in 64-bit mode.

ConnectionTester.exe is just a wrapper for both, so:

c:\> ConnectionTester.exe scott/tiger@DB1

is equivalent to:

c:\> ConnectionTester_x64.exe scott/tiger@DB1
c:\> ConnectionTester_x86.exe scott/tiger@DB1

64-bit version will be executed only on a 64-bit Windows.

Other options are used to control which providers will be tested.

• +|-ODP: Test (+) or skip (-) Oracle Data Provider for .NET
• +|-ADO: Test (+) or skip (-) Microsoft .NET Framework Data Provider for Oracle
• +|-OleDB: Test (+) or skip (-) OLE DB (Microsoft provider and Oracle provider)
• +|-ODBC: Test (+) or skip (-) ODBC drivers (Microsoft driver and Oracle driver)
• +|-DevArt: Test (+) or skip (-) Devart dotConnect
• +|-all: Test all possible Oracle providers/drivers, evaluated before other switches


• wait: Wait for key stroke after each connetion
  This option is useful when you like to analyze with tools like Process Monitor from Sysinternal
• cs: Print ConnectionString for each connection (Consider security, password is shown as clear text)
• -help or -h: Prints a help message

Switches are not case-sensitive.

Some examples:

• ConnectionTester.exe scott/tiger@DB1 +all -devart
  Testing all providers apart from Devart dotConnect. This is the default.
• ConnectionTester.exe scott/tiger@DB1 -all +odbc
  Testing only ODBC drivers.
• ConnectionTester.exe scott/tiger@DB1 -all +opd +oledb
  Testing ODP.NET and OLE DB providers.
• ConnectionTester.exe scott/tiger@DB1 +all
  Testing all providers.

Source Code

Connection Tester.csproj File

Note, the AssemblyName depends on the compile target! When you compile on AnyCPU, you get two .exe, ConnectionTester_x86.exe and ConnectionTester_x64.exe. Output file ConnectionTester_x.exe is considered as junk.

<PropertyGroup Condition=" '$(Configuration)|
$(Platform)' == 'Release|AnyCPU' ">
  <AssemblyName>ConnectionTester_x</AssemblyName>
  ...
</PropertyGroup>

<PropertyGroup Condition="'$(Configuration)|
$(Platform)' == 'Release|x64'">
  <AssemblyName>ConnectionTester_x64</AssemblyName>
  <OutputPath>..\bin\</OutputPath>
  ...
</PropertyGroup>

<PropertyGroup Condition="'$(Configuration)|
$(Platform)' == 'Release|x86'">
  <AssemblyName>ConnectionTester_x86</AssemblyName>
  <OutputPath>..\bin\</OutputPath>
  ...
</PropertyGroup>

<Target Name="AfterBuild">
  <MSBuild Condition="'$(Platform)' == 'AnyCPU'"
       Projects="$(MSBuildProjectFile)"
       Properties="Platform=x64;PlatFormTarget=x64"
       RunEachTargetSeparately="true" />
  <MSBuild Condition="'$(Platform)' == 'AnyCPU'"
       Projects="$(MSBuildProjectFile)"
       Properties="Platform=x86;PlatFormTarget=x86"
       RunEachTargetSeparately="true" />
  <Delete Files="$(OutputPath)Oracle.DataAccess.dll" />
</Target>

Testing a Provider

Each provider is a little different, I did not manage to use a single method to establish the connection. Following C# pseudo-code illustrates the core function of the program.

DbConnection con = default(DbConnection);
var str = new DbConnectionStringBuilder();
str.Add("UserId", user);
str.Add("Password", pw);
str.Add("Data Source", database);

// Try to connect via Oracle Data Provider for .NET (ODP.NET)
try {
   using ( con = new Oracle.DataAccess.Client.OracleConnection(str.ConnectionString) ) {
      con.Open();
      Console.WriteLine("Connection successful");
      con.Close();
   }
} catch ( Exception ex ) {
   Console.WriteLine("Connection failed: " + ex.Message);
}

// Try to connect via Oracle Data Provider for .NET  Managed Driver (ODP.NET Managed Driver)
try {
   using ( con = new Oracle.ManagedDataAccess.Client.OracleConnection(str.ConnectionString) ) {
      con.Open();
      Console.WriteLine("Connection successful");
      con.Close();
   }
} catch ( Exception ex ) {
   Console.WriteLine("Connection failed: " + ex.Message);
}

// Try to connect via Oracle Provider for OLE DB
str.Add("Provider", "OraOLEDB.Oracle");
try {
   using ( con = new System.Data.OleDb.OleDbConnection(str.ConnectionString) ) {
      con.Open();
      Console.WriteLine("Connection successful");
      con.Close();
   }
} catch ( Exception ex ) {
   Console.WriteLine("Connection failed: " + ex.Message);
}

// Try to connect via Microsoft OLE DB Provider for Oracle
str["Provider"] = "msdaora";
try {
   using ( con = new System.Data.OleDb.OleDbConnection(str.ConnectionString) ) {
      con.Open();
      Console.WriteLine("Connection successful");
      con.Close();
   }
} catch ( Exception ex ) {
   Console.WriteLine("Connection failed: " + ex.Message);
}
str.Remove("Provider");

// Try to connect via Microsoft .NET Framework Data Provider for Oracle
try {
   using ( con = new System.Data.OracleClient.OracleConnection(str.ConnectionString) ) {
      con.Open();
      Console.WriteLine("Connection successful");
      con.Close();
   }
} catch ( Exception ex ) {
   Console.WriteLine("Connection failed: " + ex.Message);
}

// Try to connect via ODBC Driver from Oracle
str["Driver"] = "Oracle in OraClient11g_home1";
try {
   using ( con = new System.Data.Odbc.OdbcConnection(str.ConnectionString) ) {
      con.Open();
      Console.WriteLine("Connection successful");
      con.Close();
   }
} catch ( Exception ex ) {
   Console.WriteLine("Connection failed: " + ex.Message);
}

// Try to connect via ODBC Driver from Microsoft
str["Driver"] = "Microsoft ODBC for Oracle";
try {
   using ( con = new System.Data.Odbc.OdbcConnection(str.ConnectionString) ) {
      con.Open();
      Console.WriteLine("Connection successful");
      con.Close();
   }
} catch ( Exception ex ) {
   Console.WriteLine("Connection failed: " + ex.Message);
}

All providers from Oracle are loaded with dynamic bind, so you can compile the assembly even if you do not have any Oracle client installed on your machine.

Usage of .config Files

Microsoft .NET Framework provides Publisher Policy for assemblies. When .config files are present (ConnectionTester_x64.exe.config, resp. ConnectionTester_x86.exe.config) then ConnectionTester.exe bypass publisher policies and will try to load specific version of Oracle.DataAccess.dll, resp. Oracle.ManagedDataAccess.dll. This is useful if you like to test every specific version of these DLLs.

However, typically this is not required. In this case, you can delete .config files. ConnectionTester.exe will test only one version of DLL according to publisher policies in GAC.

Example Output

c:\>ConnectionTester_x64.exe scott@DB1 -all +odp +odbc
Password: ******
Running Test on 64 bit
Found Oracle oci.dll Version 11.2

************************************************************

Oracle Data Provider for .NET (ODP.NET 1.x) not available in Oracle version 11.2

************************************************************

Try to connect via Oracle Data Provider for .NET (ODP.NET 2.0)

Try to load Oracle.DataAccess.dll (Version 2.102.*.*)
Assembly: Oracle.DataAccess, Version=2.112.4.0, 
Culture=neutral, PublicKeyToken=89b483f429c47342, processorArchitecture=Amd64
DLL-Location: Loaded from GAC
Connection successful

Try to load Oracle.DataAccess.dll (Version 2.111.*.*)
Version 2.112.4.0 already tested

Try to load Oracle.DataAccess.dll (Version 2.112.*.*)
Version 2.112.4.0 already tested

Skip Oracle.DataAccess.dll (Version 2.121.*.*)

************************************************************

Try to connect via Oracle Data Provider for .NET (ODP.NET 4.0)

Try to load Oracle.DataAccess.dll (Version 4.112.*.*)
Assembly: Oracle.DataAccess, Version=4.112.4.0, 
Culture=neutral, PublicKeyToken=89b483f429c47342, processorArchitecture=Amd64
DLL-Location: Loaded from GAC
Connection successful

Skip Oracle.DataAccess.dll (Version 4.121.*.*)

************************************************************

Try to connect via Oracle Data Provider for .NET (ODP.NET 4.0 Managed Driver)

Assembly: Oracle.ManagedDataAccess, Version=4.121.1.0, 
Culture=neutral, PublicKeyToken=89b483f429c47342, processorArchitecture=MSIL
DLL-Location: Loaded from GAC
Connection successful

************************************************************

Try to connect via Microsoft .NET Framework Data Provider for Oracle
Note: This provider has been deprecated by Microsoft!

Assembly: System.Data.OracleClient, Version=4.0.0.0, 
Culture=neutral, PublicKeyToken=b77a5c561934e089, processorArchitecture=Amd64
DLL-Location: Loaded from GAC
Connection successful

************************************************************

Try to connect via ODBC Driver: Oracle in OraClient11g_home1

DLL-Location: C:\oracle\product\11.2\Client_x64\BIN\SQORA32.DLL
Version: 11.2.0.1.0
Connection successful

************************************************************

c:\>

Points of Interest

According to my knowledge, all available providers are covered. COM based ActiveX Data Objects (ADO) uses OLE DB, Oracle Providers for ASP.NET uses ODP.NET, etc.

History

• 1.0 Initial release
• 1.1 Improved ODBC handling
• 1.2 Optionally bypass Publisher Policies in GAC in order to test dedicated every single version of Oracle DLLs