8

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!

Community
  • 1
  • 1
steve
  • 137
  • 1
  • 2
  • 10
  • Can you ping `myServer` ? did you try connecting via sqlplus ? – V4Vendetta Dec 22 '11 at 12:34
  • Can you tnsping 'myOracleDb' ? – Shai Dec 22 '11 at 12:45
  • 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 Dec 22 '11 at 12:48

3 Answers3

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...

steve
  • 137
  • 1
  • 2
  • 10
  • 1
    _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. – Michael McPherson Aug 06 '15 at 13:20
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.

Zonko
  • 3,365
  • 3
  • 20
  • 29
0

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.

tbone
  • 15,107
  • 3
  • 33
  • 40
  • `oraConnect` has been instantiated. See the `new` keyword in `using ( oraConnect = new OracleConnection...`. – Codo Dec 22 '11 at 14:01