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.

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

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.

Comments

• Anonymous
  July 22, 2009
  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?

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

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

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

• Anonymous
  April 22, 2014
  Thanks so much :)

• Anonymous
  June 08, 2014
  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?

• Anonymous
  July 06, 2014
  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.

• Anonymous
  August 30, 2014
  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

• Anonymous
  October 07, 2014
  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.

• Anonymous
  November 17, 2014
  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

• Anonymous
  March 25, 2015
  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

• Anonymous
  April 03, 2015
  translation in arabic hereرابط الدورة من هنا : http://technawi.net/?cat=25

• Anonymous
  June 03, 2015
  Thanks a ton. this saved my day!!

• Anonymous
  August 10, 2016
  hello,can you please guide me in code for login page c# using oracle database.thank you in advance