Connect to Oracle with odp.net and the OCI from C#

Question

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

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

Answer 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;";
  

Answer 3

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