Kirk Evans Blog

.NET From a Markup Perspective

Connecting to Oracle from Visual Studio

Connecting to Oracle from Visual Studio

Rate This
  • Comments 7

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:

http://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.

  • Just exactly what I needed.... it worked like a charm! Thank you... now I have some quite decent debugging capabilities and Oracle database object control from VS2008.

    You're right, we get so spoiled with the productivity of SQLServer 2008... The other day I installed the enterprise version of SQL Server 2008 (transactional and analytics (BI)) in one shot... didn't even blink... plus the sample AdventureWorks 2008... no problem at all...

    Now, I'll try to get the Entity Framework to work on this Oracle database.... any tips?

  • I had a few other obstacles to overcome, but despite that, your article saved me from a world of pain.  Thanks!!

  • help me please i'm stuck at, "Configure machine.config" and what i am going to do "tnsnames.ora"

  • Joshua - I am not available to help you troubleshoot your issue.  You might find additional help at http://stackoverflow.com.  

  • Thanks so much :)

  • hi Kirk,

    Am experincing below error as i try to add an Oracle database

    "ORA-12504: TNS:Listener was not given the SERVICE_NAME in CONNECT_DATA"

    what mistake am i making?

  • Thank you very much on this. I've Installed and configured it perfectly. And now I've retrieve the HR database from my Oracle SQL Developer into my Visual Studio 2010. Thank very much.

Page 1 of 1 (7 items)
Leave a Comment
  • Please add 3 and 4 and type the answer here:
  • Post
Translate This Page
Search
Archive
Archives