THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

John Paul Cook

Visual Studio 2012 and Oracle Development Environment

Creating a complete environment for developing .NET applications that target Oracle requires a little planning and understanding of how Oracle connectivity works. You need to be methodical and test along the way so that you aren’t trying to troubleshoot a multitude of interrelated problems at the end.

I’ve made several assumptions in writing this post:

  1. You are using 64-bit Windows 7 because you are developer with a lot of ram. I think this post will help you even if you are running Windows 8 instead of 7 because the principles are the same.
  2. You want a local copy of Oracle for testing things out because it makes initial testing much easier. That means you’re going to need the extra ram you have.
  3. You want to access your Oracle database through Visual Studio 2012.

Oracle provides you with three options for creating a local database server:

  1. 64-bit server edition
  2. 32-bit server edition
  3. Oracle XE, which is only offered as a 32-bit version

You might be able to configure Oracle XE to work on your 64-bit Windows 7 or 8 machine, but you are likely to waste a lot of time. You are trying to create a valid test environment. Your production application isn’t going to be running on Oracle XE. Nor is it likely to be running on a 32-bit version of Oracle server. Realistically, a production Oracle application is running on a 64-bit version of Oracle server. That is why this post focuses on getting 64-bit Oracle server working on Windows 7.

I didn’t provide an email address for notifications about security updates. If you do, you’ll have to configure additional settings not shown in this post. I would definitely provide an email address if this was a production instance.

image

Figure 1. Oracle 11g R2 64-bit server installation screen with no email address provided.

If you don’t provide an email address, you’ll see the following warning.

image

Figure 2. Warning about not providing an email address.

Take the default option to create and configure a database.

image

Figure 3. Take the default option.

You want a starter database (it is called HR) because having one makes it easier to test the overall installation and configuration process that is the subject of this post.

image

Figure 4. Select Desktop Class.

My machine has multiple disk drives, so I installed Oracle to the D drive instead of C. Be sure to remember your administrative password.

image

Figure 5. Oracle Enterprise Edition being installed to the D drive.

I overrode the default on the Windows Security Alert and limited communication to private networks.

image

Figure 6. Windows Firewall.

Be sure to click the Password Management button.

image

Figure 7. Click the Password Management button.

Your HR sample database has a user named HR. By default this HR user account is locked. You will save yourself grief by scrolling down and unlocking the HR account as shown below.

image

Figure 8. Be sure to unlock the HR account.

Continue on with the installation dialog boxes until the installation process is done. Reboot.

Do you need to reboot? Let’s don’t waste time arguing that point. I always reboot after installing Visual Studio, Oracle products, or SQL Server. As a developer you are going to reboot your machine sooner or later, maybe because you simply shut it down to conserve the battery or to be green. Before you begin testing and troubleshooting, your machine should be in its future state, not the just installed state. Reboot, okay?

Use SQL*Plus to test your database server configuration. You can either navigate to it or more easily just type sql in the search box and pick SQL Plus from the list.

image

Figure 9. Using search to find SQL*Plus.

Login to SQL*Plus using hr for both the user-name and the password.

SQL*Plus: Release 11.2.0.1.0 Production on Thu Dec 13 11:40:54 2012

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Enter user-name: hr
Enter password: hr

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

If you are unsuccessful in this basic connectivity test, then you need to resolve the problem before proceeding further. Obtain your Oracle error message an search for a solution. Posting to the Oracle Technology Forums is where I recommend starting. Stack Overflow is another place to post because I know that the ODP.NET team at Oracle posts there. The point is that you need to get help from people who understand Oracle.

Assuming that you remember the administrative password you entered during the server installation process, you can use the following syntax to reset an Oracle password, should the need arise. Notice that in SQL*Plus you terminate commands with a semicolon.

SQL*Plus: Release 11.2.0.1.0 Production on Thu Dec 13 12:23:43 2012

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Enter user-name: system
Enter password: passwordYouSpecifiedAtInstallTime

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter user hr identified by newPassword;

User altered.

SQL>

With basic server connectivity verified, the next step in creating your local Oracle development and testing environment is to install ODP.NET, which is a 32-bit application. It does work with 64-bit Oracle server, but it does take an extra configuration step that is covered later.

Consistent with the keep it simple philosophy, only install the ODP.NET client components as shown below.

image

Figure 10. Installing ODP.NET client software.

If you see the following error dialog box, click the Ignore button. We want to keeps things simple.

image

Figure 11. Click the Ignore button.

When the installation of ODP.NET completes, reboot.

For your next test, you need to confirm basic ODP.NET client connectivity using a UDL file which bring up the Data Link Properties dialog box. Create a file called connectionTest.udl and put it on your desktop. Double click the UDL shortcut.

image

Figure 12. UDL file on desktop. Every developer needs a UDL file!

Select the Provider tab on the Data Link Properties dialog box. Select Oracle Provider for OLE DB and click the Next button.

image

Figure 13. Select the Oracle data provider.

On the Connection tab, enter ORCL for the data source and hr for the user name and password. Click the Test Connection button.

image

Figure 14. Testing client connectivity.

If all goes well, you should see the following message:

image

Figure 15. Confirmation of basic ODP.NET connectivity.

If you do not achieve success at this step, you must resolve the problem before attempting to connect through Visual Studio.

With all of this work done and successful tests completed, will you be able to connect to Oracle using Visual Studio? No. You will have an ORA-12154: TNS:could not resolve the connect identifier specified error message.

image

Figure 16. Visual Studio doesn’t know where to find ODP.NET.

Let’s take a look at the filesystem to understand what’s going on with the Oracle products that were installed.

image

Figure 17. ODP.NET is installed under the client_1 folder and the Oracle server is installed under the dbhome_1 folder.

SQL*Plus is resolving the location of the Oracle server by looking at the tnsnames.ora file under the dbhome_1 folder. On my machine, that specific location is the D:\app\John\product\11.2.0\dbhome_1\NETWORK\ADMIN folder.

Visual Studio is looking for a tnsnames.ora file under the client_1 folder. Specifically, on my machine, the location being searched is the D:\app\John\product\11.2.0\client_1\Network\Admin folder, which doesn’t have a tnsnames.ora file.

Keeping in mind the simplicity principle, here are two easy ways to configure your machine so that Visual Studio connects to your database using ODP.NET.

  1. Copy the tnsnames.ora file under dbhome_1 to the appropriate location under client_1. The disadvantage is the potential confusion that will occur later on if the two different tnsnames.ora files get out of sync with each other.
  2. Create the system environment variable TNS_ADMIN and point it to where the server’s tnsnames.ora file is.

To set a system environment variable, enter env in the search box and then select Edit the system environment variables.

image

Figure 18. Searching for the system properties dialog box.

Click the Environment Variables button.

image

Figure 19. Click the Environment Variables button on the Advanced tab of System Properties.

Notice that there isn’t a TNS_ADMIN environment variable.

image

Figure 20. Click New under System variables to create a new system environment variable.

On my machine, the server’s tnsnames.ora file is found in the D:\app\John\product\11.2.0\dbhome_1\NETWORK\ADMIN folder, which I pasted into the Variable value textbox. If Visual Studio was open before creating the environment variable, you’ll need to close it and reopen it so that it can read the variable.

 image

Figure 21. Setting the value for TNS_ADMIN.

Once you have enabled Visual Studio to find tnsnames.ora, go to the Server Explorer and select Add Connection.

image

Figure 22. Add Connection in the Server Explorer.

You must select your Oracle instance from the dropdown list. Since the default installation options were chosen, this should be ORCL.

image

Figure 23. Selecting the ORCL instance as the data source.

Enter the user name and password and test the connection.

image

Figure 24. Connection parameters ready to be tested.

Everything should work at this point.

image

Figure 25. Life is good.

The Oracle database is now fully exposed in Server Explorer.

image

Figure 26. Oracle HR database exposed in Visual Studio 2012.

Published Wednesday, December 12, 2012 4:29 PM by John Paul Cook

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Gagan said:

OH MY GAWD. I Luv you. thank you so much. After i found out that SQLite is only good for local DB and mySQL doesnt have proper support for vs2012, this was my last option.

December 21, 2012 5:20 AM
 

abdel said:

ty guy.. you save me a lot of time

February 7, 2013 8:22 PM
 

mobasshir ahmad said:

nice article

March 4, 2013 1:24 AM
 

Larry said:

Searched high and low for this. Can't thank you enough. That one system environment entry is not talked about anywhere else and is the key to making this work.

April 1, 2013 3:43 PM
 

Hind said:

THANK YOU SO MUCH !!

April 22, 2013 7:53 PM
 

Arabinda said:

Thank u so much..It helped me alot !!!!

April 23, 2013 7:06 AM
 

cptkirkh said:

any secret on how to work with win 7 64 bit and ODAC 11 in both 32 and 64 bit worlds?

April 25, 2013 5:26 PM
 

Chetan Nandikanti said:

In my case setting Environmental Variable fixed my issue.

Thanks for the workaround!!!

April 28, 2013 12:41 AM
 

Sudhir said:

Thank you for your wonderful work... I cannot say how happy I am ... After struggling for 2 days... it seems that I hit the jackpot.... Thank you so much......

May 8, 2013 1:57 AM
 

Tyler said:

You are FANTASTIC!  I've had countless issues on multiple machines wasting days to eventually get it to work without knowing what I actually did to pull it off.  I'll be bookmarking this page!  GREAT WORK!

May 8, 2013 11:39 AM
 

Sumeet said:

Believe me sir,

I thought i am a big idiot after not able to connect, but now, just because of your blog, i solved the problem as if it was a piece of cake :D

A million Thanks :)

May 9, 2013 9:13 AM
 

sunck said:

I dont think your process is right,my computer's folder is right set ,but vs2012 also can't connect oracle

May 15, 2013 3:15 AM
 

MINAR said:

I haven't got enough words to thank u... great work there :)

May 21, 2013 6:00 AM
 

Sumee said:

Environmental Variable was the problem in my case..i just cud not find the solution anywhere else..thnx a lot

May 22, 2013 6:00 AM
 

rubenscf2 said:

Gracias :)... me sirvio  mucho uen aporte

May 27, 2013 8:14 PM
 

Tâm said:

Thank you very very much!

I have searched for this post very hard.

June 18, 2013 11:20 AM
 

Abhi said:

Hi John Paul Cook,

I'm new to this place/blog so pls forgive me if I'm posting this question at the wrong place.

I've installed VS2012 Ultimate + Oracle 11g + Oracle tool for VS2012(ODTwithODAC1120320_32bit.zip) downloaded from Oracle site.

The Problem -

I've followed the steps that you've shown above and for me as well it worked perfectly fine for creating a Data Connection, under the Server Explorer in VS2012. In VS2012 I can see my db tables and everything.

But problem arises when I try to create a ADO .NET Entity Data Model i.e.

I right Click on myProject >> Add >> New Item

then

Visual C# Items >> ADO.NET Entity Data Model

Here I add a Model.edmx then on next screen I select Generate from database, Click Next button, then I click the New Connection button and here I'm taken to the Choose Data Source window but I CANNOT see Oracle as the data source??? I can only see SQL Server entries namely these 4 -

1. Microsoft SQL Server

2. Microsoft SQL Server Compact 4.0

3. Microsoft SQL Server Database File

4. <other>

Now I've been thru numerous forums (even Oracle forum) but none of them seem to explain clearly how to get Oracle as a data source here.

Can you please help me figure this out? I'd really appreciate any help on this matter.

And btw thanks to you for posting such a good tutorial(sort of) for integrating Oracle and Visual Studio

Thank you.

August 6, 2013 10:12 AM
 

Cereal said:

I faced a connection problem with my VS2012 application and my Oracle Database on a Virtual Machine under Windows 7 x64 : setting Environmental Variable TNS_ADMIN fixed my issue.

Thanks a lot !

September 3, 2013 5:13 AM
 

MFudge said:

Amazing timesaver! Thank you so much!

November 20, 2013 9:23 AM
 

Rob said:

Unfortunately does not work anymore under windows 8.1

December 3, 2013 9:09 AM
 

Sikandar said:

great job your article help me a lot in solving my problem

December 15, 2013 11:15 AM
 

Martin Acero said:

great job brother

January 1, 2014 5:49 AM
 

nithya said:

tnks a lot

March 20, 2014 8:42 AM
 

Phan Ngoc Huy said:

Thank you so much !

June 30, 2014 3:33 AM
 

Josh said:

I have a problem when use odp.net for entity framework 5.0

The error happened when use more than 2 Any() in 1:n relationship

E.g.   Var result = object.Entity1.Any(e1=>e1.entity2.Any(e2=>e2.entity3.Any(e3=>e3.id != null)))

Entity1 1:n entity2, entity2 1:n entity3

Thank you

July 31, 2014 7:04 AM

Leave a Comment

(required) 
(required) 
Submit

About John Paul Cook

John Paul Cook is both a Registered Nurse and a Microsoft SQL Server MVP experienced in Microsoft SQL Server and Oracle database application design, development, and implementation. He has spoken at many conferences including Microsoft TechEd and the SQL PASS Summit. He has worked in oil and gas, financial, manufacturing, and healthcare industries. Experienced in systems integration and workflow analysis, John is passionate about combining his IT experience with his nursing background to solve difficult problems in healthcare. He sees opportunities in using business intelligence and Big Data to satisfy healthcare meaningful use requirements and improve patient outcomes. John graduated from Vanderbilt University with a Master of Science in Nursing Informatics and is an active member of the Sigma Theta Tau nursing honor society. Contributing author to SQL Server MVP Deep Dives and SQL Server MVP Deep Dives Volume 2.
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement