1 Reply Latest reply: Nov 21, 2012 1:55 PM by 869484 RSS

    ODP.NET with Database 11g

    900677
      I downloaded and installed the "Personal Edition" of "Oracle Database 11g Release 2" on my desktop machine that runs 64-bit Windows 7. It seems to be running fine. I can do queries with SQL PLus and do stuff with Enterprise Manager.

      Then, I downloaded and installed "ODAC 11.2 Release 3 ... with Oracle Developer Tools for Visual Studio". That is generally working, too, I can use Server Explorer in Visual Studio 2010 to run queries, etc. I can also access my Oracle database with basic client code, like this really simple example...

      static void Main(string[] args)
      {
      Console.WriteLine("Oracle Solution 1 - Test");
      DateTime startTime = DateTime.Now;

      try
      {
      Console.WriteLine("Instantiate Connection");
      using (OracleConnection cnn = new OracleConnection("Data Source=ORCL;User Id=HR;Password=hrpw;"))
      {
      Console.WriteLine("Instantiate Command {0}", new TimeSpan(DateTime.Now.Ticks - startTime.Ticks));
      using (OracleCommand cmd = new OracleCommand("select first_name, Last_name from employees", cnn))
      {
      Console.WriteLine("Open {0}", new TimeSpan(DateTime.Now.Ticks - startTime.Ticks));
      cnn.Open();
      Console.WriteLine("Execute {0}", new TimeSpan(DateTime.Now.Ticks - startTime.Ticks));
      using (OracleDataReader reader = cmd.ExecuteReader())
      {
      Console.WriteLine("Read {0}", new TimeSpan(DateTime.Now.Ticks - startTime.Ticks));
      while (reader.Read()) Console.WriteLine("{0}\t{1}", reader[0], reader[1]);
      }
      }
      }
      }
      catch (Exception ex)
      {
      Console.WriteLine(ex.Message);
      Console.WriteLine(ex);
      }
      Console.WriteLine(" {0}", new TimeSpan(DateTime.Now.Ticks - startTime.Ticks));
      Console.ReadLine();
      }

      Within the Visual Studio 2010 debugger, this runs in approx. .17 seconds, which seems about right. But when I run without the Debugger, the same code takes over 2 minutes and 10 seconds. When I run the executable entirely outside of Visual Studio, the result is virtually the same...well over 2 minutes for something that should take about a quarter of a second.

      According to the time hacks in my Console.Writeline statements, the hang-up is in instantiating the connection, not in the Open statement or in actually running the query.

      Here's my tnsnames.ora file...

      LISTENER_ORCL =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))


      ORACLR_CONNECTION_DATA =
      (DESCRIPTION =
      (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      )
      (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
      )
      )

      ORCL =
      (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
      (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl.168.1.100)
      )
      )

      Why does this simple code sample perform so badly unless I run it with the debugger? I'd really appreciate any suggestions anyone can offer.
        • 1. Re: ODP.NET with Database 11g
          869484
          Bump! I am having exactly this same issue. Also running 64-bit Windows 7. Have been using Oracle Express 11g for a long time very happily, but with with Java. Jdbc connections all work great, so no problem with port conflicts or anything. In the debugger of Visual Studio 2010, everything works silky smooth just like the original poster pointed out. But the minute you try to run the executable by itself, the constructor for the connection class gets stuck for about 2 minutes and 20 seconds. But AFTER that delay, then everything works great and the actual data query to the database can be performed multiple times with no delay whatever. Even re-running exactly the same initial connection code. It seems like this is a problem with loading some run-time discoverable library. In the visual studio debugger, it must have a built in reference to the necessary dll, but when running the code it does not. But adding the client side libraries that some forums claim ODP.Net depends on should have solved the issue in that case, and it didn't. Here are the libraries I added:

          oci.dll
          ociw32.dll
          Oracle.DataAccess.dll
          orannzsbb11.dll
          oraocci11.dll
          oraociei11.dll
          oraociicus11.dll
          OraOps11w.dll

          It does not seem to matter whether I target 2.0 or 4.0 or whatever. I can't build towards "any CPU" at all, so possibly because of my references, it forces the program to build to (Active x86).

          This did not get solved by my dropping a whole set of oracle client dll files into the same directory as my executable. I do see my log files like:

          C:\oraclexe\app\oracle\diag\rdbms\xe\xe\alert\log.xml

          filling up with exceptions:
          O/S-Error: (OS 1) Incorrect function. !
          OER 7451 in Load Indicator : Error Code = OSD-04500: illegal option specified

          but all reports an that are that this is because oracle express is only a 32 bit program and it is fundamentally incompatible with my 64 bit machine, but those probably aren't related to my data queries which is for an oracle server which is not even on my machine. The delay happens whether I try to connect to oracle express on my machine or a published database on a server.

          Seeing as the original poster hasn't gotten a response in over a year, I'm not hopeful that anyone can solve this issue for us, but I thought I would just show that this is still a problem for people.

          Of course I seem to be using version oracle 11.2.0, so maybe I just need to download some more recent client side libraries.