3

I have been reading about how to connect to my oracle database from my C# win application, but I keep “hitting the wall”. I have decided to use odp.net and OCI, such that the client computer not needs to install a client, but I can’t get it to work.

I have a small test application, the code I shown below and in my solution I have added the following dll’s from oracle OCI: oci.dll, orannzsbb11.dll and oraociicus11.dll. They are all placed together with the final .exe file.

Test Code:

private static string CONNECTION_STRING =
                  "User Id=hr;Password=hr;Data Source=(DESCRIPTION=" +
                  "(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1521))" +
                  "(CONNECT_DATA=(SID=XE)));Connect Timeout=15;";

        static void Main(string[] args)
        {
            try
            {
                using (var conn = new OracleConnection(CONNECTION_STRING))
                {
                    conn.Open();
                    Console.WriteLine("Connection is: {0}", conn.State.ToString());
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
        }

The problem occurs already in the using(…) statement, the program just stop working and I get no response. What is the magic that I need to do to get the OCI to work???

aweis
  • 5,350
  • 4
  • 30
  • 46
  • Is the _OracleConnection_ class part of the System.Data.OracleClient or the Oracle.DataAccess.Client namespace? (Your description is somewhat confusing because ODP.NET is the heaviest client available.) – Codo Apr 13 '11 at 10:08
  • It is the Oracle.DataAccess.Client - the only thing that I want to achive is to be able to connect to an oracle database without installing a client! – aweis Apr 13 '11 at 10:13
  • You don't use ODP.NET, do you? If you had installed ODP.NET, you would have gone through a full-blown Oracle client installation that includes OCI and many registry settings. But I doesn't seem that this is the road you want to go. – Codo Apr 13 '11 at 10:17
  • My plan was to use odp.net, and yes i have gone through the full-blown installation to get all the dll's. I use opd.net such that i can use the Factory Instance, because i have a generic database layer, becuase my application uses multiple DBMS's – aweis Apr 13 '11 at 10:21

3 Answers3

2

To be able to use ODP.NET without installing the full blown client, you need to use the Oracle Instant Client packages (you cannot just copy the libraries from a complete client):

  • Check here for a description of the requirements.

  • Starting with Oracle v10, I would strongly recommend using EZCONNECT to simplify your connection string. How about this:

    private const string CONNECTION_STRING="User Id=hr;Password=hr;"+
   +"Data Source=127.0.0.1:1521/XE;Connect Timeout=15;";
Community
  • 1
  • 1
Mac
  • 8,191
  • 4
  • 40
  • 51
  • I have read this article and tried it out, but with no luck. When I run from VS2008 it works (i think that it somehow uses my installed client), but the exe file failes. Actually it just stop working at the "new OracleConnection(CONNECTION_STRING)" line. The app does not crash it just dont move further than this statement, do you have an idea of why it does this? – aweis Apr 13 '11 at 12:16
  • Try to activate traces: http://download.oracle.com/docs/html/E10927_01/featConfig.htm (see the TraceLevel and TraceFileName parameters). And do use the Instant Client libraries... – Mac Apr 14 '11 at 12:32
  • I used the Xcopy version and found out that the code was working, but it just used about a minut to find out that a log folder was missing, and then it proceeded executing the code. So now i have manually added the folder and it all seems to work. – aweis Apr 14 '11 at 21:07
1

Normally when using OCI, or Oracle database products in general, the ORACLE_HOME environment variable should be defined and pointing to your oracle installation. Next to the libraries Oracle does use some other support files and it is searching for them in ORACLE_HOME. Normally the LD_LIBRARY_PATH is defined as ORACLE_HOME/lib. Try using the Instant Client, that is most likely better than hand picking a few libs. A nice article about how to get it to work is here: Installing Oracle instantclient basic and instantclient sqlplus on win32 You can leave out the part about sqlplus.

And from the instance-client page on otn:

Instant Client Downloads Please note that Instant Client is provided under a separate OTN Development and Distribution License for Instant Client that allows most licensees to download, redistribute, and deploy in production environments, without charge. Please consult the license and your legal department for clarification, if necessary. For more information on Instant Client, see the official Instant Client site.

It looks like you are allowed to redistribute the instance-client.

  • I have already tried the instant client, but I can’t get it to work. How do I setup my win application such that is uses the instant client, and how do I ship it together with the application, such that a user not need to do anything? – aweis Apr 13 '11 at 10:23
  • Make sure that the Oracle libs have their environment variable ORACLE_HOME defined for them. To test, easiest is to start with the command shell and setup your environment in there, before starting your app. I don't know about how Oracle does this exactly but I think you are not allowed to combine the client with the app. For this it is better to consult an Oracle representative, they have solutions that might be useful for you. –  Apr 13 '11 at 10:30
  • @aweis I updated the answer with more info about redistribution and a reference to a howto. –  Apr 13 '11 at 10:45
1

Read the installation instructions from Oracle for ODAC found here. This also discusses common setup issues.

You'll also need to include a reference to Oracle.DataAccess in your solution and "using Oracle.DataAccess.Client;" For your connection, you may want to use an Oracle SID that can be resolved via your tnsnames file (try tnsping from cmd prompt).

tbone
  • 15,107
  • 3
  • 33
  • 40