Oracle Connection Tester

Testing various Oracle client installation and drivers in Windows

Installing an Oracle client can be a challenge. Sometimes you install the Oracle client and drivers but your application fails to connect to the Oracle database. This small tool helps you to find the problem in your Oracle client installation.

In principle the application runs this (fairly simplified) code:

 var connectString = "Driver={Oracle in OraClient11g_home1};Uid=<usernme>;Pwd=<secret>;DBQ=<database>";
 var con = new System.Data.Odbc.OdbcConnection(connectString);
 con.Open();
 
 var connectString = "Driver={Microsoft ODBC for Oracle};Uid=<usernme>;Pwd=<secret>;Server=<database>";
 var con = new System.Data.Odbc.OdbcConnection(connectString);
 con.Open();
 
 var connectString = "Provider=OraOLEDB.Oracle;Data Source=<database>;Password=<secret>;User ID=<usernme>";
 var con = new System.Data.OleDb.OleDbConnection(connectString);
 con.Open();
 
 var connectString = "Provider=MSDAORA;Data Source=<database>;Password=<secret>;User ID=<usernme>";
 var con = new System.Data.OleDb.OleDbConnection(connectString);
 con.Open();
 
 var connectString = "Data Source=<database>;User ID=<usernme>;Password=<secret>";
 var con = new System.Data.OracleClient.OracleConnection(connectString);
 con.Open();
 
 var connectString = "Data Source=<database>;User ID=<usernme>;Password=<secret>";
 var con = new Oracle.DataAccess.Client.OracleConnection(connectString);
 con.Open();
 
 var connectString = "Data Source=<database>;User ID=<usernme>;Password=<secret>";
 var con = new Oracle.ManagedDataAccess.Client.OracleConnection(connectString);
 con.Open();
 
 var connectString = "Data Source=<database>;User ID=<usernme>;Password=<secret>";
 var con = new Devart.Data.Oracle.OracleConnection(connectString);
 con.Open();
 
 var connectString = "Provider=OracleClient;Data Source=<database>;User ID=<usernme>;Password=<secret>";
 var con = new Devart.Data.Universal.UniConnection(connectString);
 con.Open();
 
 var connectString = "Driver={Devart ODBC Driver for Oracle};Uid=<usernme>;Pwd=<secret>;Server=<database>";
 var con = new System.Data.Odbc.OdbcConnection(connectString);
 con.Open();
 
 var connectString = "Data Source=<database>;User ID=<usernme>;Password=<secret>";
 var con = new DDTek.Oracle.OracleConnection(connectString);
 con.Open();
 
 var connectString = "Driver={DataDirect 8.0 Oracle Wire Protocol};Uid=<usernme>;Pwd=<secret>;ServerName=<database>";
 var con = new System.Data.Odbc.OdbcConnection(connectString);
 con.Open();
 
 var connectString = "Driver={Easysoft ODBC-Oracle Driver};Database=<database>;Uid=<usernme>;Pwd=<secret>;Server=<database>;SID=<database>";
 var con = new System.Data.Odbc.OdbcConnection(connectString);
 con.Open();
 
 var connectString = "Driver={Easysoft ODBC-Oracle WP Driver};Database=<database>;Uid=<usernme>;Pwd=<secret>;Server=<database>;SID=<database>";
 var con = new System.Data.Odbc.OdbcConnection(connectString);
 con.Open();
 
 var connectString = "Data Source=<database>;User=<usernme>;Password=<secret>";
 var con = new System.Data.CData.OracleOci.OracleOciConnection(connectString);
 con.Open();
 
 var connectString = "Driver={CData ODBC Driver for Oracle OCI};Data Source=<database>;User=<usernme>;Password=<secret>";
 var con = new System.Data.Odbc.OdbcConnection(connectString);
 con.Open();
 
 var connectString = "Driver={Simba Oracle ODBC Driver};TNS=<database>;UID=<usernme>;PWD=<secret>";
 var con = new System.Data.Odbc.OdbcConnection(connectString);
 con.Open();
 

Common problems in Oracle client installations

• You mix 32-bit and 64-bit assemblies.

  All assemblies of your application, i.e. the Oracle client, the driver and your application itself must run in the same architecture (i.e. 32-bit or 64-bit). Note, the "Oracle Data Provider for .NET Managed Driver" does not depend on 32-bit/64-bit.

• Oracle client and "Oracle Data Provider for .NET Driver" are not the same version. The versions have to match exactly

• The required driver is not installed.

  Your application may use a certain driver - you see there are many of them - but the required driver is not installed. ODBC or ODP.NET providers are not included in bare Oracle Instant client.

  Note, in 12.1 and later ODP.NET is not added to GAC anymore, see Installation Does Not Register Oracle Data Provider for .Net in the GAC. You need to register the assembly manually.

Java/JDBC based Oracle drivers are not in scope of this tool.

Problems with alias resolution, typically related to tnsnames.ora file, are not in scope of this tool.

Problems with ODBC DSN Data Source definitions are not in scope of this tool.

How to use it

c:\>ConnectionTester -h

Usage:

ConnectionTester.exe [<logon>] [+|-ODP] [+|-ODPM] [+|-ADO] [+|-OleDB] [+|-ODBC] [+|-DevArt] [+|-Progress] [+|-CData] [+|-all] [wait] [cs]

   <logon> is <username>[/<password>]@<connect_identifier> (same as SQL*Plus)

   +|-ODP: Test (+) or skip (-) Oracle Data Provider for .NET
   +|-ODPM: Test (+) or skip (-) Oracle Data Provider for .NET Managed Driver
   +|-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, Oracle, Devart, Progress, Easysoft, Simba ODBC driver if installed)
   +|-DevArt: Test (+) or skip (-) Devart dotConnect
   +|-Progress: Test (+) or skip (-) Progress DataDirect Connect for ADO.NET
   +|-CData: Test (+) or skip (-) CData ADO.NET Provider for Oracle OCI
   +|-Simba: Test (+) or skip (-) Simba ODBC Driver (only relevant in combination with '+ODBC')
   +|-all: Test (+) or skip (-) all possible Oracle providers/drivers, evaluated before other switches
   Default tested drivers are: ODP ODPM ADO OleDB ODBC

   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
   
   Default drivers to be tested: ODP, ODPM, ADO, OleDB, ODBC

Example:

ConnectionTester.exe scott@ora1 -oledb -odp +devart

The ConnectionTester.exe is just wrapper for 32-bit and 64-bit call. Running

ConnectionTester.exe scott/tiger@ora1

is eqivalent to

ConnectionTester_x64.exe scott/tiger@ora1
ConnectionTester_x86.exe scott/tiger@ora1

Supported native drivers

• Oracle Provider for OLE DB
• Microsoft OLE DB Provider for Oracle (only for 32bit, deprecated, does not work anymore with Oracle Client 18c or newer)
• Microsoft .NET Framework Data Provider for Oracle (deprecated)
• Oracle Data Provider for .NET (ODP.NET)
• Oracle Data Provider for .NET, Managed Driver (ODP.NET Managed Driver)
• dotConnect for Oracle from Devart (formerly known as OraDirect .NET from Core Lab)
• dotConnect Universal from Devart (uses deprecated System.Data.OracleClient)
• DataDirect Connect for ADO.NET from Progress
• ADO.NET Provider for Oracle OCI from CData
• Oracle Data Provider for .NET (ODP.NET) Core is not supported yet

Supported ODBC drivers

• ODBC Driver from Oracle
• ODBC driver from Microsoft (only for 32bit, deprecated, does not work anymore with Oracle Client 18c or newer)
• ODBC driver from Devart
• ODBC driver from Progress
• ODBC Oracle Driver from Easysoft
• ODBC Oracle WP Driver from Easysoft
• ODBC Driver for Oracle OCI from CData
• ODBC Oracle Driver with SQL Connector from Magnitude, formerly Simba

All ODBC drivers are tested directly, i.e. "DSN-less". It only tests the actual ODBC driver, not the ODBC DSN Data Source. Thus no DSN entry is required in your ODBC Data Source Administrator.

Known issues

When ODBC driver "Microsoft ODBC for Oracle" for 32-bit is tested and fails then error message window pops up. I don't managed to catch this error for console output.

Testing different client versions

By default this tool test only one version of each Oracle driver. You may require to test different versions or Oracle client. Version of .NET assemblies loded from GAC are determined by policy files.

In order to test all versions of Oracle Data Provider for .NET use the .config files (included in "ConnectionTester-Full.zip") and set <publisherPolicy apply="no"/> accordingly. Multi version testing is only available for the "Oracle Data Provider for .NET Driver" and "Oracle Data Provider for .NET Managed Driver" providers.

In most use cases the .NET .config files are not required. Release "ConnectionTester.zip" comes without them.

Deploy assembly

Feel free to compile the source by yourself. Oracle based applications may run in 32-bit or 64-bit mode, this tool intents to test both.

You should build the solution only with AnyCPU. Visual Studio will create the files in solution root bin folder.

• ConnectionTester_x64.exe: The 64-bit version
• ConnectionTester_x86.exe: The 32-bit version
• ConnectionTester.exe: A wrapper to run both versions with one call
• ConnectionTester_x64.exe.config: .NET config file for 64-bit version
• ConnectionTester_x86.exe.config: .NET config file for 32-bit version

File ConnectionTester_x.exe is just used temporarily while build.

All drivers and providers are loaded dynamically. In order to build the assembly your developing PC does not require any Oracle client installation.

Installation

This project provides only stand-alone .exe, no setup. Purpose of this tool is just to test the Oracle client installation on your machine. Thus it does not perform any modifications on your system.