How to Install Oracle ODBC Driver on Windows 10

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

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.

Now you should be able to pick or select the TNS service. Thanks for reading this how to install Oracle ODBC driver on Windows 10. Cheers

9 Comments

  1. Hi,
    I have trouble with install ODBC driver /client on Windows 10 64 bit machine. I have installed Oracle 19c Db on one PC, and from my PC I want connect to it , My PC is Windows 10 64 bit, but because my develeloping application is 32 bit I install ODBC 32 bit. ALso Visual C++ redist.is 32 bit. From ODBC Administrator data source I can create DSN, and also test connection, all works without problems.
    But when I run my test program (programmed in Alaska Xbase++ and SQLExpress++ library) connect first after instalation, and no anymore, still get error
    = SQL ERROR ===========
    Date: 20200528 11:33:55
    C:\TESTORACLE\SQLBROW.EXE, Thread: 1
    Windows 8 06.02 Build 09200, SQLXpp: 3.3.00, Runtime: 2.00.554
    SQLState: IM003, ErrorCode:160
    Specified driver could not be loaded due to system error 1114: A dynamic link library (DLL) initialization routine failed. (Oracle in instantclient_19_6, C:\Oracle\Product\Client\instantclient_19_6\SQORA32.dll).

    I tryedl everything, and no solution. This looks to first “some” open SQLORA32.DLL and not release it, or I do not know something stay blocked and cannot connect again.
    No help reinstall driver, unistal/install, nothing. Interesting is that test connection from ODBC driver assistent still work,only my app cannot connect more than once, sometimes after some delay I connect but again still blocked.
    Interesting is that on third PC with also W10 64 bit this works for me that I mean some can be in Windows configuration, some process or dll open and stay opened, or rewrite some parameter in registry, do not know.
    Please thanks for any advice.

  2. Thanks for the article! One more possible solution is ODBC Driver for Oracle
    https://www.devart.com/odbc/oracle/
    Devart ODBC Driver for Oracle provides a high-performance and feature-rich connectivity solution for ODBC-compliant applications to access Oracle databases from Windows, macOS, or Linux, both 32-bit and 64-bit. Full support for standard ODBC API functions and data types implemented in our driver makes interaction of your database applications with Oracle fast, easy and extremely handy.

2 Trackbacks / Pingbacks

  1. Cannot connect to Oracle Database with Power Query - Boot Panic
  2. Oracle odbc driver 64 bit excel

Leave a Reply

Your email address will not be published.


*