Error when trying to connect to Oracle 10g database from C# program employing minimal set-up configuration

Question

I'm experiencing an error when trying to connect to a remote Oracle 10g database from a C# 2008 Express Edition application I'm developing. I'm trying to use a minimalist, non-intrusive approach to the development with a view to ClickOnce deployment to user workstations.

In respect of the above I've investigated the following documents (amongst others..) -

What is the minimal setup required to deploy a .NET application with Oracle client 11?

http://jeremybranham.wordpress.com/2011/04/25/oracle-instant-client-with-odp-net/

http://ora-00001.blogspot.com/2010/01/odpnet-minimal-non-intrusive-install.html

http://splinter.com.au/using-the-new-odpnet-to-access-oracle-from-c

Connect to Oracle with odp.net and the OCI from C#

In view of the error I've experienced I've created a simple test app. consisting of a single (wpf) page with one button. In the click-event of the button I attempt to create a connection to an Oracle database -

private void button1_Click( object sender, RoutedEventArgs e )
{
    OracleConnection oraConnect;

    // string previously used OK in other projects
    string connectionString = "Data Source=" +
           "(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = myServer)(PORT = 1521)))" +
           "(CONNECT_DATA =(SERVICE_NAME = myOracleDb)))" +
           ";Password=myPw;User ID=myID;";

    using ( oraConnect = new OracleConnection( connectionString ) )
    {
        try
        {
            if ( oraConnect.State == ConnectionState.Closed )
            {
                oraConnect.Open();
                MessageBox.Show( "oraConnect is attempting to open.." );
            }
            else
                MessageBox.Show( "oraConnect open to DB: " + oraConnect.ServerVersion.ToString() );
        }
        catch ( NullReferenceException nullExcept )
        {
            MessageBox.Show( "Caught error: ." + nullExcept.ToString() );
        }
        catch ( OracleException dbEx )
        {
            MessageBox.Show( "OraException - " + dbEx.Message.ToString());
        }
        catch ( Exception ex )
        {
            Exception current;
            current = ex;

            while ( current != null )
            {
                current = current.InnerException;
            }

            MessageBox.Show( "Db base exception - " + ex.GetBaseException().ToString() );
        }
        finally
        {
            oraConnect.Close();
        }
    }
}

Following the information in the above articles I've ensured that the following Dll's are in my "bin" folder -

• oci.dll
• ociw32.dll
• orannzsbb10.dll
• oraocci10.dll
• oraociicus.dll
• msvcr71.dll

(the last named in desperation...) and have referenced 'Oracle.DataAccess.dll'.

The error message (at 'catch ( OracleException dbEx )') is -

"Oracle.DataAccess.Client.OracleException was caught
  Message=""
  StackTrace:
       at Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, String procedure)
       at Oracle.DataAccess.Client.OracleException.HandleError(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, Object src)
       at Oracle.DataAccess.Client.OracleConnection.Open()
       at OracleConnectionTest.Window1.button1_Click(Object sender, RoutedEventArgs e) in C:\Documents\Visual Studio 2008\Projects\OracleConnectionTest\OracleConnectionTest\Window1.xaml.cs:line 69
  InnerException: "

Line 69 is 'oraConnect.Open();'.

In addition, the following is reported -

"((Oracle.DataAccess.Client.OracleException)($exception)).DataSource' threw an exception of type 'System.NullReferenceException".

I'm assuming from the NullReferenceException within the datasource that the problem lies in one of the dlls' (?) as I 'new' the OracleConnection above before trying to reference it.

In addition, the code execution jumps the 'catch ( NullReferenceException nullExcept )' and goes straight to the OracleException catch.

Sorry for rambling on but hope this makes sense? Any help/advice appreciated!

Can you ping myServer ? did you try connecting via sqlplus ? — V4Vendetta
– V4Vendetta, Commented Dec 22, 2011 at 12:34
The NullReferenceException isn't the cause of the problem. It occurs when you inspect the exception in VisualStudio. It's a debugging artifact and can be ignored. Unfortunately, the real exception seems to contain no error message whatsoever. Or have you overlooked it somewhere? — Codo
– Codo, Commented Dec 22, 2011 at 12:48

steve · Accepted Answer · 2013-12-04 09:52:36Z

2

OK, very late getting back to this for which many apologies!

In the interim, our DB has been upgraded(!) and in changing the dll list to include -

oraocci11.dll
oraociccus11.dll
OraOps11w.dll
orannzsbb1.dll

from the '10' versions, still with no success, I edited App.xaml (following an extensive search here and on the web) with the following -

    <system.data>
      <DbProviderFactories>
         <add name="OracleClient Data Provider"
              invariant="System.Data.OracleClient"
              description=".Net Framework Data Provider for Oracle"
              type="System.Data.OracleClient.OracleClientFactory, System.Data.OracleClient, Version=2.0.0.0, Culture=neutral, PublicKeyToken=##################"/>
      </DbProviderFactories>
   </system.data>

   <!-- publicKeyToken obtained using Reflector to investigate dll -->
   <runtime>
      <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
         <dependentAssembly>
            <assemblyIdentity name="Oracle.DataAccess"
                              publicKeyToken="##################"
                              culture="neutral"/>
            <bindingRedirect oldVersion="10.2.0.100" 
                             newVersion="2.112.2.0"/>
         </dependentAssembly>
      </assemblyBinding>
   </runtime>

The bindingRedirect did the trick!

The dll's appear to be extremely dependent on compatible version numbers

Wish I could say I really understood how this works but it does and I now have working connections...

answered Dec 4, 2013 at 9:52

steve

1471 gold badge2 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Michael McPherson Over a year ago

The dll's appear to be extremely dependent on compatible version numbers This. Oracle is nothing if not EXTREMELY touchy, and I've had whole servers bail out on nothing more than an X.X.1/X.X.2 version mismatch. That was going to be my debugging suggestion, and I'm glad this is resolved. You should accept your own answer so other people can find it.

Zonko · Accepted Answer · 2012-05-23 08:42:18Z

1

It happened to me.

After a bit of voodoo, I deleted this key from my registery : HKEY_CURRENT_USER\Software\ORACLE and everything worked fine again.

answered May 23, 2012 at 8:42

Zonko

3,7233 gold badges22 silver badges34 bronze badges

Comments

tbone · Accepted Answer · 2011-12-22 14:37:01Z

Make sure ODAC is setup properly. I would suggest to use TNSNAMES (you shouldn't have all that info in a connection string imo). See the TNSNAMES setup section of this document (11.2). Also see bottom section for common connection issues

Once that's done, it should be as easy as putting your connection string in the settings property of your project and doing:

oraConnect = new OracleConnection(Properties.Settings.Default.MyConnString);

In your example, your oraConnect hasn't been instantiated (you just have "OracleConnection oraConnect"), so the "new OracleConnection" part that fails results in a null reference exception (if I understand your explanation of where its breaking anyway). Debugger in VS should help here as well ;)

EDIT: You may want to setup a simple test console app with just open/close connection. This might eliminate any noise other than getting your ODAC setup properly. Something like (untested, assume tnsnames setup):

using ...

namespace Testbed {
  class Program {
    static void Main(string[] args) {
      try {
        string connStr="User Id=my_user;Password=my_pass;Data Source=my_sid;";
        OracleConnection oraConnect = new OracleConnection(connStr);
        oraConnect.Open();
        Console.WriteLine("Opened Connection");
        oraConnect.Close();
        Console.WriteLine("Complete");
        Console.ReadLine();
      catch (System.Exception e) {
        Console.WriteLine(e.Message);
        Console.ReadLine();
      } ...

Try running that and report what comes back from console.

oraConnect has been instantiated. See the new keyword in using ( oraConnect = new OracleConnection....

Collectives™ on Stack Overflow

Error when trying to connect to Oracle 10g database from C# program employing minimal set-up configuration

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related