How To Create Dsn For Oracle

Hello everyone, welcome to my blog. In this article, I am going to show you how to install Oracle ODBC Driver on Windows 10. I have a working Oracle Database 12c on another computer on my home network. I am trying to connect to my Oracle database from Microsoft Access using ODBC connection. By default, Windows 10 does not shipped with Oracle ODBC connection utility. In other word, I was unable to connect to my Oracle database from MS Access.

Installing Oracle ODBC Driver on Windows 10 is a bit tricky. It's not as simple as installing PostgreSQL ODBC driver or MySQL ODBC Driver. I was struggling to get it works. The following method works very well on my home network. So let's dig inside.

My Setup

I have a Windows 10 running Oracle Database 12.2
I am going to connect to my Oracle Database via ODBC from another computer, running Windows 10 as well.

Steps to Install Oracle ODBC Driver on Windows 10

Step 1. Install Oracle Instant Client Basic

Oracle ODBC Driver needs this Instant Client package to run. So, we need to download this package from the official download website of Oracle.

https://www.oracle.com/technetwork/database/database-technologies/instant-client/downloads/index.html

Select the correct architecture of your system either 32 bit or 64 bit. In this example I am going to install the 32 bit version since I have MS Office 32 bit version.

Next, download the Instant Client Basic package as shown on the picture above. Also, you will need to download the Instant Client SDK and Instant Client ODBC as shown below

So now we have three packages:

instantclient-basic-nt-12.2.0.1.0.zip
instantclient-sdk-nt-12.2.0.1.0.zip
instantclient-odbc-nt-12.2.0.1.0-2.zip

Now Create a new folder for the Oracle client. For example, in this case I created a new folder : C:\Oracle\Product\client . Put all those three zip packages in that new directory. Next, extract them all. It will produce a new directory called: instantclient_12_2 . If you download the different version, for example version 18.3, it will create new directory instantclient_18_3 .

Step 2. Add the Oracle Client directory to the PATH environment

Now we need to add our Oracle Client directory to the Windows PATH environment. Right-click My Computer >> Advanced System Settings >> Environment Variables

On the System Variables, click New and then enter the following details:

Variable name: OCI_LIB32
Variable Value: C:\Oracle\Product\client\instantclient_12_2

Change C:\Oracle\Product\client\instantclient_12_2 with your actual Oracle Client folder.

Next step is to add the Oracle Client directory to system path. Double click the path and create a new entry as follow

Step 3. Install the Oracle ODBC Client

Now open the folder instantclient_12_2 , in your Oracle Client folder created on the Step 1 and double click the file odbc_install .

Please note that there is no GUI installation wizard when you open this file.

Step 4. Create a new ODBC Data Source on Windows 10

To create a new Oracle ODBC Data Source, open Control Panel >> Administrative Tools >> ODBC Data Sources (32bit). If you are using 64 bit version, choose the ODBC Data Sources (64bit).

Click Add to add a new DSN. Select "Oracle in instantclient_12_2" from the list. Click Finish. Next, enter the Oracle ODBC Driver Configuration. Give the new Data Source Name, select TNS Service Name and User ID. Click Test Connection to validate the connection to the Oracle Database Server.

Troubleshooting

On my first attempt, I cannot get the list of the TNS Service when creating a new ODBC Connection. To solve this, we need to copy or create the tnsnames.ora in the Oracle Client Directory. Open Notepad and then copy and paste these lines and then save it as tnsname.ora. Save the file in the Oracle Client directory ( C:\Oracle\Product\client\instantclient_12_2 ).

This is the content of my tnsnames.ora which is works well

LISTENER_ORCL =
          (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
ORACLR_CONNECTION_DATA =
          (DESCRIPTION =
          (ADDRESS_LIST =
          (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
          )
          (CONNECT_DATA =
          (SID = CLRExtProc)
          (PRESENTATION = RO)
          )
          )
ORCL =
          (DESCRIPTION =
          (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.51)(PORT = 1521))
          (CONNECT_DATA =
          (SERVER = DEDICATED)
          (SERVICE_NAME = orcl)
          )
          )
MYDB01 =
          (DESCRIPTION =
          (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.51)(PORT = 1521))
          (CONNECT_DATA =
          (SERVER = DEDICATED)
          (SERVICE_NAME = mydb01)
          )
          )

After that, we need to tell the system to use this tnsnames.ora config file. Open the Environment Variable again and add a new entry with the following value:

Variable Name: TNS_ADMIN
Variable Value: C:\Oracle\Product\client\instantclient_12_2

Don't forget to change the variable value with your Oracle Client Directory.