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:
- 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.
- 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.
- You want to access your Oracle database through Visual Studio 2012.
Oracle provides you with three options for creating a local database server:
- 64-bit server edition
- 32-bit server edition
- 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.
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.
Figure 2. Warning about not providing an email address.
Take the default option to create and configure a database.
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.
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.
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.
Figure 6. Windows Firewall.
Be sure to click the Password Management button.
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.
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.
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.
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.
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.
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.
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.
Figure 14. Testing client connectivity.
If all goes well, you should see the following message:
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.
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.
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.
- 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.
- 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.
Figure 18. Searching for the system properties dialog box.
Click the Environment Variables button.
Figure 19. Click the Environment Variables button on the Advanced tab of System Properties.
Notice that there isn’t a TNS_ADMIN environment variable.
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.
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.
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.
Figure 23. Selecting the ORCL instance as the data source.
Enter the user name and password and test the connection.
Figure 24. Connection parameters ready to be tested.
Everything should work at this point.
Figure 25. Life is good.
The Oracle database is now fully exposed in Server Explorer.
Figure 26. Oracle HR database exposed in Visual Studio 2012.