1

I have some code that uses ODP.Net 

using (OracleConnection connection = new OracleConnection(connectionString))
{
  connection.Open();
  boxCommand = new OracleCommand(sql, connection);

  OracleDataAdapter boxAdapter = new OracleDataAdapter(boxCommand);
  DataTable boxTable = new DataTable();        

  boxAdapter.Fill(boxTable);
}

I then get an error below on a production server. The test server is fine.

Error Image

I don't understand as its complaining about a connection not being open but if there was a problem it should occur at the point my Open is called not on the Fill. Also I thought Fill was supposed to open the connection anyway.

Can anyone suggest what might be going on?

UPDATE: From the comments I tried adding this but same problem:

using (OracleConnection connection = new OracleConnection(connectionString))
{
  connection.Open();
  while(connection.State != ConnectionState.Open)
  {
     connection.Close();
     connection.Open();
  }
  boxCommand = new OracleCommand(sql, connection);

  OracleDataAdapter boxAdapter = new OracleDataAdapter(boxCommand);
  DataTable boxTable = new DataTable();        

  boxAdapter.Fill(boxTable);
}

UPDATE 2 : I have added logging and the Validate Connection = true to the connection string and I know the connection state is open, the box command was done, the adapter created and the table created, it definitely errors on the Fill

Jon
  • 38,814
  • 81
  • 233
  • 382
  • Can you put in a debugging message to write out the value of connection.State? – cadrell0 Apr 03 '12 at 15:27
  • 1
    I hope that this could be of help [connection-lost-contact](http://stackoverflow.com/questions/3370078/oracle-dataaccess-client-oracleexception-ora-03135-connection-lost-contact) – Steve Apr 03 '12 at 15:31
  • Is that your real/actual code, or just an example? (I suspect it is a rigged up example, because `boxTable` would loose scope without being used after filled.) I have the feeling there is some detail missing from the code snippet... – CodingWithSpike Apr 03 '12 at 15:37
  • @rally25rs You are totally wrong, The OP mentioned that the error occurs at the `Fill` not after nor before.. I think the link in Steve's comment explains it all.. –  Apr 03 '12 at 15:41
  • @Steve Why doesnt the Fill open the connection though? – Jon Apr 03 '12 at 16:24
  • if the connection is `Broken` then you have to `Close` the connection first before you can `Open` the connection again. –  Apr 03 '12 at 16:28
  • Are you proposing this? connection.Open(); while (connection.State != ConnectionState.Open) { connection.Close(); connection.Open(); } – Jon Apr 03 '12 at 16:38
  • I guess that will work, did you try it? –  Apr 03 '12 at 16:44
  • The OP made it sound like this happens *every time*, not on occasion. (The OP doesn't say "sometimes" or "after a while") so I doubted the connection would always be broken from the server every time. Actually I was guessing the "real" code might have been doing the .Fill after the ending curly-brace of the `using` which would have disposed/closed the connection before the Fill happened. – CodingWithSpike Apr 03 '12 at 17:28
  • I will try with the change in the connectionstring as mentioned in the above link. It's difficult to say what's is the best workaround when the error (if there is really an error in ODP) happens inside the Fill method. However, we can only hope that, `Validate Connection = true` is the best way to tell at ODP to check the state of the connection, just before acting on the connection itself. – Steve Apr 03 '12 at 19:27
  • I tried my loops as shown in my comment above and same error! – Jon Apr 03 '12 at 19:30
  • I also tried using Validate Connection = true and still no joy. Any other ideas? – Jon Apr 05 '12 at 11:35
  • @Jon - I noticed you accepted my answer, but just wondered, did reinstalling fix your problem (not sure if you did that or just gave up)? – dcp Apr 05 '12 at 18:51
  • @dcp Found out the program was referencing 10.2.100 but the Oracle client was 10.1.200 – Jon Apr 05 '12 at 21:52

1 Answers1

0

Can you try installing the latest version of ODP.Net and see if you still have the problem? You should be able to run latest ODP.Net even against older Oracle DBs (10g in your case).

EDIT: Since you're restricted to a specific ODP.net version, here are some other things to try:

  • Make sure the proper Oracle folders are at the beginning of your system path. For example, I have the Oracle folders at the beginning of my path, like this (I have ODP.net installed in c:\oracle\ora11g and I also have Oracle 10g Express Edition installed, but note that the ODP.net folders are first:

    c:\oracle\ora11g\product\11.1.0\client_1;c:\oracle\ora11g\product\11.1.0\client_1\bin;C:\oracle\ora10g\bin;

    I've seen situations where Oracle does weird things and won't work properly if the path isn't correct.

  • Try reinstalling the specific ODP.net version you are using. This should clean things up and hopefully resolve your issue.

dcp
  • 54,410
  • 22
  • 144
  • 164