using Oracle.DataAccess.Client; // This goes up the top
...
string connstring =
  "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=myhost)(PORT=1527))" +
  "(CONNECT_DATA=(SID=mysid)));User Id=myuserid;Password=mypassword;";
using (OracleConnection conn = new OracleConnection(connstring))
{
  conn.Open();
  string sql = "select distinct owner from sys.all_objects order by owner";
  using (OracleCommand comm = new OracleCommand(sql, conn))
  {
    using (OracleDataReader rdr = comm.ExecuteReader())
    {
      while (rdr.Read())
      {
        Console.WriteLine(rdr.GetString(0));
      }
    }
  }
}

53 Comments

1. Wil on August 21st, 2009

   Don't forget to rename the ".dll.dbl" files to ".dll"; otherwise you'll get the exception.
   Thank you Chris. This was very helpful.

2. Fidel on September 11th, 2009

   Thankyou Chris, this solution is great and works really well. In fact, my multithreaded app (which does a LOT of queries) performs 57% faster when using your redist pack

3. Miguel on September 29th, 2009

   dude you are my hero i cant thank you enough great tip!!!

4. kts on October 7th, 2009

   Hi Chris, thanks for the tip. this is really great.

   I do however have a question, we only include the dll and not referencing it through the project, i.e. but instantiate it using the System.Data.provider factories, then we do need to use gacutil to install it in the global assemblycache. Is that correct?

   SO as an example if I refer this dll in my config as:

   Now if I call using the .NET standard classes (System.Data), I can instantiate the oracle connection/command etc by getting its provider factory through below call:

   DbProviderFactories.GetFactory(ConfigurationManager.ConnectionStrings[_dbName].ProviderName);

   where _dbName just some variable of the database name.

   However, this means that the DLL stills needs to be installed in the GAC using gacutil.exe.

   THanks in advance for reading ths.

5. Chris Hulbert on October 7th, 2009

   Hi 'Kts',
   I believe you're on the right track there. That does give you the flexibility to use the same code for any database (sql, oracle, postgres, etc). But i think you'd then have to work with the System.Data.Common.* types (eg System.Data.Common.DbConnection). These types are a bit more generic and may have less of the oracle-specific data types, however if you can get it to work then its a great idea!
   Also, i'm personally not a fan of the GAC but if it works for you then go for it.

6. Sunit Joshi on October 13th, 2009

   Hi Chris
   Thanks for the informative write up. I downloaded the XCOPY version of Oracle 11g ODAC 11.1.0.7.20 and with that the only large file is the oraociei11.dll which is 114MB and looks like that is reqd in the EXE folder. Did you notice the same thing ?

   thanks
   Sunit

7. Chris Hulbert on October 13th, 2009

   Hi Sunit,
   I wasn't aware of an XCOPY version. Give it a try with only that file, maybe it'll work? Let us know if it works.
   114megs is pretty big though, the files i described above are only ~30megs.
   Still, maybe it supports features that my bare-bones method doesn't. Could be worth remembering!
   Cheers

8. Sunit Joshi on October 14th, 2009

   Ok I did find the ClientLite version and could get the files. Now it works in interactive mode, however when I start it as a scheduled job, I get this error

   TNS:listener was not given the SERVICE_NAME in CONNECT_DATA

   I did add a TNS_ADMIN variable pointing to the tnsnames.ora folder. My connection string is
   string.Format("Data Source={0};User Id=/;", server);

   Any hints are greatly appreciated.

   thanks
   Sunit

9. Sunit Joshi on October 14th, 2009

   Update…If I set the TNS_ADMIN variable in code, then it seems to be working. This is what I do for my process's env variables before I inoke any Oracle calls;

   Environment.SetEnvironmentVariable("PATH", Environment.CurrentDirectory, EnvironmentVariableTarget.Process);

   Environment.SetEnvironmentVariable("TNS_ADMIN", tnsAdmin, EnvironmentVariableTarget.Process);

   In my case I have Oracle Client 10.2.0.4 installed on 64-bit Vista. I'm using OPD.NET 11.1.0.7

   I'm going to test the release version and let you know.

   thanks
   Sunit

10. Chris Hulbert on October 14th, 2009

    Sunit,
    The way you've written your connection string is making oracle look for a TNSNAMES.ORA file.
    If you write your connection string like the way i did it in the blog entry, you won't have that problem.
    Eg a connection string like this:

    Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=myhost)(PORT=1527))
    (CONNECT_DATA=(SID=mysid)));User Id=myuserid;Password=mypassword;

11. Thomas on October 14th, 2009

    Hi Chris,

    I tried all the things you mentioned above step by step and it still doesn't work for me.
    I keep getting the exception, 'The provider is not compatible with the version of Oracle client' no matter what.

    In my case, it appears that the Oracle Data Access components need to be installed on the target machine for this code to work and the reason for this is that it still relies on the ODP.NET 2.111.7.20 registry key being in place. As far as I can see, the only useful thing within that key is the path to the DLLs: C:\ORACLE\11.1\odac\bin. As soon as I remove that key from the registry, the code stops working.

    I was wondering when you mentioned “Simple Xcopy deployment” as one of the benefits of this whether you take the registry entry into consideration or not. Does it need to be there for this to work? Can you please clarify it for me?

12. Chris Hulbert on October 15th, 2009

    Hi thomas,
    I had the same problem that you're describing. There are 3 things to check:
    * Did you get all the dll's from the same download from oracle's site?
    * Did you place all these dll's in the same folder as your C# executable?
    * Did you rename the .dbl files to .dll as described in my post?

    Let me know how you get on.

13. harshad on October 15th, 2009

    hi
    i m using oracle 10.1.0.2 so what can i do
    use the way you mentioned above????

14. Chris Hulbert on October 16th, 2009

    Harshad - that should work just fine. As far as i know, if you're using the latest client dll's then you should be able to connect to any version of oracle.
    Give it a try and let us know.

15. harshad on October 24th, 2009

    hi
    Chris Hulbert,
    thanks it works for me tooo……….

16. fudata on October 29th, 2009

    Hi Chris,

    I did three checks you suggested for Thomas but still gets the same error " 'The provider is not compatible with the version of Oracle client'. I have Oracle client 10.0.2 version installed together with 11g. Is that causing the problem, you think?

17. harshad on November 6th, 2009

    hi
    chris
    i have developed wpf app in Microsoft Visual Studio Team System 2008 Development Edition in C# and oracle 10 g and deployed app as per you told it works fine it gives error to view crystal report
    error :-
    login failed
    plz helppp…..

18. Chris Hulbert on November 9th, 2009

    Hi Harshad, as far as i know crystal reports connects to the database using its own code so this technique is a bit too low-level to help with it. I don't have much crystal experience, but you could try asking on StackOverflow.com they're pretty helpful there.

19. Wagner on November 17th, 2009

    Hi Chris and Kts,

    I am working on the database application, where we have to support different databses (Oracle, MS SQL etc) and in your discussion on this article, you wrote something about system.data.common namespace which one can use to connect to any database. Could you kindly give me some kind of code snippet ?

20. Chris Hulbert on November 17th, 2009

    G'day Wagner,
    Here's some code that i think is what you want. Everything uses the system.data.common, and the only oracle specific part is the value of the 'provider' string:

    using System;
    using System.Collections.Generic;
    using System.Text;

    using System.Data.Common;

    namespace OdpTest
    {
    class Program
    {
    static void Main(string[] args)
    {
    string connectionString = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=MYHOST)(PORT=1527))(CONNECT_DATA=(SID=MYSERVICE)));User Id=MYUSER;Password=MYPASS;";
    string provider = "Oracle.DataAccess.Client.OracleConnection, Oracle.DataAccess";

    Console.WriteLine("Connecting");

    using (DbConnection conn = (DbConnection)Activator.CreateInstance(Type.GetType(provider), connectionString))
    {
    conn.Open();
    Console.WriteLine("Connected");
    string sql = @"
    select distinct owner
    from sys.all_objects
    order by owner";
    Console.WriteLine("Looking for all DB owners, wait a few seconds:");
    using (DbCommand comm = conn.CreateCommand())
    {
    comm.CommandText = sql;
    using (DbDataReader rdr = comm.ExecuteReader())
    {
    while (rdr.Read())
    {
    string owner = rdr.GetString(0);
    Console.WriteLine("{0}", owner);
    }
    }
    }
    }
    }
    }
    }

21. Wagner on November 18th, 2009

    Thanks Chris… But I am doing it in the other way, by saving providername in the config file and reading the config file during runtime. I want to have one generic class that take care of atleast SQ Server and Oracle. Oracle gives back a ref cursor where as SQL Server returns integer value back. The problem is how to fill dataset with ref cursor. any idea ?

22. Chris Hulbert on November 18th, 2009

    Hi Wagner,
    If you modify the code above so that the value of the 'provider' string came from a config file, you'd be half way there.
    The generic class you're asking about is above in the 'DbConnection conn' variable, which will handle both SQL and Oracle (and more).
    As for filling a dataset, i'm not sure if you can directly create a 'DbDataAdapter' so you'd have to use the specific oracle or sql data adapters. Again, you could put the class name in a config file and instantiate it using Activator.CreateInstance, similarly to above.
    Anyway, hope that helps. I'm confident you could achieve what you're attempting.

23. Wagner on November 18th, 2009

    Hi Chris. I have just read this from oracle website

    "For the Fill method to populate the DataSet properly, the SelectCommand of the OracleDataAdapter must be bound with an output parameter of type OracleDbType.RefCursor. If the Fill method is successful, the DataSet is populated with a DataTable that represents a REF CURSOR."

    but there is no code example on HOW to do it ? do you have some idea ?

    "

24. eti on November 18th, 2009

    Hi,
    Great post, exactly what i needed to know. Also for me only oci.dll, oraociicus11.dll and OraOps11w.dll wore needed. I hope for the 64bit version the same files are ok.

    Thanks

25. Chris Hulbert on November 19th, 2009

    Hi Wagner,
    Sorry mate, you've lost me there, i don't think i can help.
    Having said that, i avoid using datasets and data adapters for precisely the reason that you are having problems with them. And i've managed to do just fine without them.
    Anyway good luck.

    And Eti, that's great, glad to hear.

26. Wagner on November 20th, 2009

    Thanks wagner for your reply.. Could you please guide me on ho you could have obtained data from SQL Server and Oracle without using data adapters and other things ? If possible a piece of code will prevent further questions.

27. Wagner on November 20th, 2009

    to make it a bit clear, the stored procedures are called which specify REF CURSOR as outputparameter (only in oracle's case)

28. Wagner on November 20th, 2009

    sorry mate about the name mistake in my first post… it should be like Thanks Chris ad not wagner

29. Chris Hulbert on November 24th, 2009

    Hi Wagner,
    Something like this would do the trick. The FindAll function returns a list of people. This list would be usable to bind to gridviews or the like so would be almost as useful as a dataset, minus all the complication. Hope that helps.

    public class People
    {
    public static List FindAll()
    {
    string connectionString = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=MYHOST)(PORT=1527))(CONNECT_DATA=(SID=MYSID)));User Id=MYUSERID;Password=MYPASSWORD;";
    string provider = "Oracle.DataAccess.Client.OracleConnection, Oracle.DataAccess";
    List list = new List ();

    using (DbConnection conn = (DbConnection)Activator.
    CreateInstance(Type.GetType(provider), connectionString))
    {
    conn.Open();
    using (DbCommand comm = conn.CreateCommand())
    {
    comm.CommandText = "select id,first_name,last_name from people";
    using (DbDataReader rdr = comm.ExecuteReader())
    {
    while (rdr.Read())
    {
    Person p = new Person();
    p.id = rdr.GetInt32(0);
    p.firstName = rdr.GetString(1);
    p.lastName = rdr.GetString(2);
    list.Add(p);
    }
    }
    }
    }

    return list;
    }
    }

    public class Person
    {
    public int id { get; set; }
    public string firstName { get; set; }
    public string lastName { get; set; }
    }

30. Gareth Hill on December 3rd, 2009

    Was having the same problem and tried all of the above but could not get it to work. I eventually restarted IIS (wish I'd thought of it earlier) and all was fine!

31. Chris Hulbert on December 3rd, 2009

    Good for you gareth, I expect that was due to the DLLs not being loaded dynamically because they're native code not managed.
    Having said that, i've only used this code in a console app, not a asp.net web site.

32. Otto on December 16th, 2009

    Chris,

    Please forgive what may be an obvious question from a newbie, but I'm having trouble with the simple deployment method described in this blog post. I'm still getting "The provider is not compatible with the version of Oracle client" exceptions. Could there be a problem if there is a different Oracle client version already installed on the target machine?

33. Chris Hulbert on December 16th, 2009

    Hi Otto,
    Is this a winforms or asp.net or console application?
    If winforms/console, can you list the contents of the folder where your executable is placed?
    If asp.net, can you list the contents of the BIN folder underneath your application root?
    When you say 'target machine' do you mean the oracle server, or the computer your application is running on?
    You can also try stackoverflow.com if you need a quicker answer than i may be able to provide.
    All the best

34. Otto on December 17th, 2009

    Problem was an issue with my deployment manifest file, which was keeping the dll's from being included in the ClickOnce deployment. Manifest file is straightened out, and all is working well now, even on target machines with other Oracle clients already installed.

    Thanks.

35. Chris Hulbert on December 17th, 2009

    That's good to hear otto, thanks for the feedback.

36. Jing Ding on December 30th, 2009

    I was looking for the above DLLs in my locally installed Oracle Client home, and copying to web server's BIN folder. I could not find oraociicus11.dll. So I just copied the other DLLs. It still worked.

37. andy on January 8th, 2010

    your post helps me a lot. thanks

38. Sylvain on January 9th, 2010

    Hi,

    I try to make the same thing with a 10.2.0.4 client version.

    I've take the 10.2.0.4 Instant Client Basic Lite dll's and the 10.204 Oracle.DataAcess.dll,but I still gets the same error " 'The provider is not compatible with the version of Oracle client'.
    But when I test your configuration on a pc with a 10.2.0.4 client installed, it's work.

    All the dll are in my exe directory. I don't understand that is wrong.

    Can you help me ?

39. Chris Hulbert on January 9th, 2010

    Sylvain,
    First up, what are the name of all the dll's in the same folder as your EXE? Maybe you're missing one or two, and so it looks in the system folder for the missing dll/dlls and on your pc it finds an incompatible version, and on the other pc it finds a compatible version.
    Cheers

40. Farzad Badili on January 24th, 2010

    Thank you Chris, This solution is great and works really well. I cant thank you enough great tip.

41. MOjo on January 26th, 2010

    Thanks for this post, man!

    Finally i got everything working.

    But there is an more easy way to get the required .dlls. Yout can download ODAC1110720Xcopy.zip, extract it, and search in it for *.dll - every .dll you need is somewhere in this directory… most of them in instantclient_11_1.

    I got the error "provider not compatible", but as i deleted the file oraociei11.dll, the error was gone.

    Thanks!

42. Chris Hulbert on January 27th, 2010

    Thanks mojo, very helpful.
    However, i tried that method, and it wouldn't work without the 'oraociei11.dll' file. Which is an issue to me, because that file is 114 megs! Thats huge for just a database driver!

43. Bob on January 28th, 2010

    The people where I work are hesitant to install 11g client along side the current 10g client so I tried your method here but it didn't work as hoped.

    I'm trying to use ODP.NET is an ASP.NET application. I don't have any access to the server and IIS unfortunately. I created a Bin folder, dumped the files you mentioned there, and tried it. Trying to connect using ODP.NET only returns a blank exception (the most unhelpful kind really). On top of that the way I was connecting, using System.Data.OracleClient, stopped working. It now says OCIEnvCreate returns -1. And finally, oci.dll can't be deleted from the Bin folder. It says it's in use. What is ASP.NET doing with that file that it'd be in constant use?

    Ah, and lest I forget, I did get someone to restart IIS and that didn't work.

    Do you have any ideas? Since I found this through Stackoverflow I figure you also know I asked this question there as well: http://stackoverflow.com/questions/2142877/oracle-oci-dll-in-asp-net-bin-directory-cant-be-deleted-doesnt-work

44. Guilherme on January 29th, 2010

    Hi ,
    I was getting the error " 'The provider is not compatible with the version of Oracle client'. just after I copy msvcr71.dll it worked

45. Rowel on April 27th, 2010

    finally a working solution!!! you're a savior man. many thanks!

46. fred on August 10th, 2010

    thanks so much, after I spent 1.5 days trying to connect to Oracle 10g from visual studio 2008…

47. Ken on August 12th, 2010

    Thanks for the example.

48. Alan on August 23rd, 2010

    Brilliant,

    Works well, but on a fresh machine build we HAD to include msvcr71.dll also.
    This file is not required for 64-bit.

    We use only the following files from the Oracle 11.2.0.1 distro;
    msvcr71.dll - 340Kb
    oci.dll - 964Kb
    Oracle.DataAccess.dll - 992Kb
    oraociicus11.dll - 32,152Kb
    OraOps11w.dll - 404Kb

    Overall a 40Mb increase in application size.

    For 64-bit servers, just replace all the same files from the 64bit distro with the exception of msvcr71.dll.

    Find this method to be about 10% faster over the Microsoft provider.

    Thanks for the blog. Saved us a lot of hassle.

49. Evan Bonnett on August 31st, 2010

    Thanks dude. This worked for me. Saved me a lot of time and pain

50. Sundeep on September 3rd, 2010

    The XCopy Instant Client seems to work for me during compilcation. But, the OracleConnection.Open is throwing a Object Reference not set exception.

    My Connection string looks like this,
    "DATA SOURCE=//testservice.edu:1605/hscp;PASSWORD=userid;USER ID=password"

    Please help!

51. Chris Hulbert on September 3rd, 2010

    Sundeep, your connection string should be the following:

    "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=testservice.edu)(PORT=1605))(CONNECT_DATA=(SID=hscp)));User Id=userid;Password=password;"

52. Sundeep on September 3rd, 2010

    Chris,

    Still no luck. I am getting a blank exception when trying to Open the connection and the inner exception is saying 'Object Reference Not set Exception'. I will be happy to provide any information you want. Please help me figure this out.

    Thank You
    Sundeep

53. Chris Hulbert on September 4th, 2010

    Hi Sundeep,

    You should ask on stackoverflow.com - you'll definitely get an answer there, much quicker than i can help you. I suspect you're missing some of the oracle DLL's.

    Good luck

Leave a Reply