Kirk Evans Blog

.NET From a Markup Perspective

Connecting to Oracle from Visual Studio

Connecting to Oracle from Visual Studio

Rate This
  • Comments 13

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:

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.


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


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


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> =
      (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/ 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=, 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).


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.


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


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


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  

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

  • i installed VS 2010 after that i installed oracle 10 Express  edition. i want to connect Vs2010 with oracle 10g. i want to add reference  Oracle.DataAccess.Client. but my reference  doesn't Show Oracle.DataAccess.Client. please give me solution

  • Its ok i got it . But i have one problem with this.

    mt database connection is closing once i restarted visual studio. So that i can't able to make setup file.

    please help me.

  • Mr.Kirk i want to know where is the exact location that machine.config you edited, because there is 2 framework and 2 kind of machine.config which is machine.config.comments and machine.config.default..  i'm running win8.1  64bit and i use oracle 10g for VS2012 express edition or perhaps you had better oracle version for me to try :D

  • thank u for this tuto but plz tell me, are this tables  from your database or they come with  oracle installation because i have a data base base wich not contains any table but when i follow this steps  i find many tables : custemers, region..  in ma data base and even if i add any table to data base i found only  this tables

  • translation in arabic here

    رابط الدورة من هنا :

  • Thanks a ton. this saved my day!!

Page 1 of 1 (13 items)
Leave a Comment
  • Please add 6 and 2 and type the answer here:
  • Post
Translate This Page