Connecting to Oracle from Visual Studio

Man, I am spoiled.  I am spoiled by how simple the free edition of SQL Server 2008, SQL 2008 Express, is to set up and connect to.  I found myself needing to connect to an Oracle database this week.  I haven’t had to connect to an Oracle database in years, I forgot how painful this can be.  If you find that you need to set up a connection to an Oracle database for your application, and assuming you are able to install the Oracle 10g client and tools locally on one machine for development purposes, here are some steps to try to ease the learning curve a bit.  Judging from the number of forum posts on this, it seems I am not the only one.

Install Oracle 10g Express

There is a free version of Oracle that you can download to test your application against.  I downloaded the database from:

https://www.oracle.com/technology/software/products/database/xe/htdocs/102xewinsoft.html

I accepted all defaults through the installation process, and provided a password for the system and sys accounts.  Let’s assume something ridiculously simple like <“pass@word1>” for the sake of the rest of this post. In the Windows start menu, find the command to go to the database home page.

image

You are prompted for a username and password.  Choose “sys” and use the password from above (remember, we used <“pass@word1>” as a demo here).

Choose Administration, then Database Users, then click on the HR icon.  Provide a password of your choosing, and set the Account Status to “unlocked”. 

Install the Oracle Developer Tools

This one had me stumped, I went through several miscues before I figured out that you don’t need to install the ODAC or Oracle Client bits.  What was confusing is if I needed the 10.x version of the developer tools, or if I can use the 11.x version of the tools.  Turns out you can use the 11.x version just fine, as found in this link:

ODAC 11.1.0.6.21 with Oracle Developer Tools for Visual Studio

This downloads the a ZIP file which you need to extract.  Once extracted, run setup.exe.  In the radio checkbox asking if you want to install client bits or server bits, you are only going to install the client bits.  Accept the rest of the defaults and finally close the program.

Configure TNSNames.ora

If, like me, you expected to complete the above installation, open up Visual Studio, and get started, you are in for a disappointment.  First, you need to tell Oracle how to find your database.  Somewhere in the Oracle docs I saw the key.  Go to the following directory (replace the folder “kirke” with your folder name, of course).

C:\app\kirke\product\11.1.0\client_1\Network\Admin\Sample

Copy the tnsnames.ora file from the Sample directory to its parent directory (remember to replace “kirke” with your folder name).

C:\app\kirke\product\11.1.0\client_1\Network\Admin

Then open up tnsnames.ora and make some edits.  Here is what mine ended up as.  My machine name is “EVANS1”, and Oracle Express was installed to port 1521 (the default). 

<data source alias> =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = EVANS1)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

Configure machine.config

Still not there yet.  Next, you need to configure the Oracle provider in machine.config.  Just to spare the extra mouse click here (if you are reading this, you are probably already frustrated to begin with) the magic entry to add to machine.config in the configuration/system.data/DbProviderFactories section is:

<add name="Oracle Data Provider for .NET" invariant="Oracle.DataAccess.Client" description="Oracle Data Provider for .NET" type="Oracle.DataAccess.Client.OracleClientFactory, Oracle.DataAccess, Version=2.111.6.20, Culture=neutral,  PublicKeyToken=89b483f429c47342" />

Set the SID Environment Variable

This next part had me stumped, until I ran across this forum post that showed how to troubleshoot and pointed to a likely cause.  The fix was to set an environment variable called “oracle_sid” and set it to the value of “xe” (since we are using Oracle Express, this is the default).

image

Connect Using Visual Studio

The last step is to throw chicken bones, do a rain dance, and cross your fingers to hope you connect.  In Visual Studio 2008, click the “Connect to Database” button in the Server Explorer pane.  Choose the data source, making sure to use the Oracle Database as the data source, and Oracle Data Provider for .NET as the data provider.

image

That pulls up another window for provider-specific connection settings.  I specified the user name as “HR” and our password that we created earlier (using the Oracle web page dialog, remember?)

image

Once I stumbled through all these settings, I could finally connect to Oracle using the Visual Studio server explorer pane.

image

This is just SO much simpler when you use the freely available SQL Server 2008 Express.