Oracle Database Connection String for ODP.NET using TNSNames.ora

If you are using the TNSNames.ora file for connecting to your Oracle database, you might be a bit confused about using the following Oracle connection string as listed here: https://www.connectionstrings.com/oracle/


Normally, when you see something like that, you think immediately of setting up a DSN in ODBC Data Sources, right? Well, in this case, you do not need to set up anything in ODBC Data Sources! You simply configure your Net Service Name in your Oracle Client so that you have one or more entries in your tnsnames.ora file as follows:

Now, the Data Source attribute in your Oracle connection string simply corresponds to one of the entries in your tnsnames.ora file (in this case, there are entries for ORCL11G and ORCL respectively).

That is all there is to understanding how to build your Oracle database connection strings using TNS Names!

Oracle Database Connection String for ODP.NET without TNSNames.ora file

I was recently attempting to follow the Oracle database connection string listed on https://www.connectionstrings.com/oracle/ using the ODP.NET Data Provider in order to establish a database connection to my Oracle 11g server, but I was soon faced with this error message:

ORA-12514: TNS: listener does not currently know of service requested in connect descriptor

I couldn't figure out the cause of this problem, since this same connection information is listed in the Oracle documentation (under the section for "Specifying the Data Source Attribute")!  https://docs.oracle.com/html/E10927_01/featConnecting.htm

Fortunately, one of my co-workers suggested using the following alternative connection string by replacing SERVICE_NAME with SID:


Making this simple change resolved my connectivity issues!

Where are the 32-bit Oracle ODBC Drivers?

If you are working with a 64-bit OS, you will encounter a problem which was not typically faced in the days of 32-bit OSes--namely, that you will have a separate set of 64-bit ODBC Drivers vs. a set of 32-bit ODBC Drivers.

So, when you type odbcad32.exe at the Run Command, you are ACTUALLY opening up the 64-bit ODBC Drivers that have been installed on the machine and NOT the 32-bit ODBC Drivers.

The 32-bit drivers can actually be found by typing the following path at the Run Command: \windows\SysWOW64\odbcad32.exe.  Therefore, if you are looking for the 32-bit Oracle drivers, you will only find these ODBC Drivers available in the 32-bit ODBC Drivers area.

When you open the ODBC Dialogs as well, you should look closely at the Title of the Dialog as well:

When you open the default ODBC Dialog, you will see a title of "ODBC Data Source Administrator (64-bit)".

However, when you open the 32-bit ODBC Dialog, you will see the following title instead: "ODBC Data Source Administrator (32-bit)"

If the titles match up, then you know you are in the correct place!!

Installing Oracle Client 12c on Windows 10

If you need to work with Oracle on your development machine, at some point in time, you are going to need to install the Oracle client on your machine.

Fortunately, if you install newer versions of Oracle clients, they will usually remain backwards-compatible with older versions of Oracle server.  For example, you can STILL install and Oracle 12c client while working with an Oracle 11g R2 database!

Without further ado, below you can find the steps to install Oracle Client 12c on your Windows 10 system:

For whatever reason, Oracle does not ship with necessary Visual C++ Prerequisites with its installer, so you will have to also separately install the Visual C++ Redistributable.

Otherwise, you may end up with errors such as the following when attempting to set up your ODBC DSNs for Oracle:

Based on the Oracle Pre-installation requirements, you can install a number of Visual C++ installers on your system ranging from Visual C++ 2010, to 2012 to Visual C++ 2013: http://docs.oracle.com/database/121/NTCLI/pre_install.htm#NTCLI1257

However, in my experience, the ONLY version of Visual C++ I was able to get to work on my machine was Visual C++ 2010 which you can download from here: https://www.microsoft.com/en-us/download/details.aspx?id=5555

NOTE: If you are installing the 32-bit Oracle Client, then you will need to install the Visual C++ x86 installation and likewise if you are installing the 64-bit Oracle Client, you will need to install the Visual C++ x64 installation.

Importing an Oracle Dump (.dmp) file for Oracle 11g R2

I will be the first one to admit that I am not an Oracle guru by any means since I primarily stick to the Microsoft stack (aka Microsoft SQL Server) so developing with Oracle for any project is always a bit of a struggle and a learning curve for me.

One of the common tasks that nearly every Oracle developer will have to face at some point in time is how to restore an Oracle Dump (.dmp) file!

Unfortunately, the documentation on just how to accomplish this is relatively sparse and doing this within Oracle is not as straightforward as restoring a backup into SQL Server.

After quite a bit of searching on the Internet and searching through various forums such as Stack Overflow, I was able to come up with a workable solution.

First of all, in order to get everything prepped for the Oracle import, you will have to set up a user for the import.  To save you a few headaches, it is best to try and open the .dmp file in Notepad++ or a similar editor to figure out which user the .dmp file was exported from and create it accordingly.

This set of commands can be run in SQLPlus, SQL Developer, Toad or any other Oracle IDE you prefer:


Once you have that user in place, you will have to finally perform the import.  One of the caveats to doing this, however, is that you MUST DO THIS ON THE SERVER!!  Attempting to perform this import on a client machine will fail miserably and leave you scratching your head as to why you cannot get the import commands to work!

Therefore, once you get the .dmp file uploaded to the server and establish an RDP session into your server, you can run the following command at the Windows command prompt.  (I tried getting it to run outside of the Windows command prompt such as in SQL Developer or Toad, but I could never get these IDEs to recognize the command):


That should be all that is necessary to get your .dmp file onto your Oracle 11g R2 instance!

Installing Oracle Database server 11g R2 on Windows

If you need to develop against an Oracle Database backend, you will probably want to be able to setup your own Oracle Database for development and testing.

Even though Oracle Database 12c has been out for quite some time, there will probably be numerous occasions where you have to install the older version of Oracle Database 11g R2 instead.  To do so, you simply have to download the Oracle Database 11g downloads from here (http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html), extract the .zip files and then run setup.exe:

Installing Oracle Database server 12c on Windows

If you need to develop against an Oracle Database backend, you will probably want to be able to setup your own Oracle Database for development and testing.

Surprisingly, installing Oracle Database 12c on Windows is very easy!  You simply have to download the Oracle Database 12c downloads from here (http://www.oracle.com/technetwork/database/enterprise-edition/downloads/database12c-win64-download-2297732.html), extract the .zip files and then run setup.exe: