ODBC and Oracle

<- Windows

Overview

Configuration of ODBC on Oracle systems can be very confusing. The following is designed to navigate you quickly through this maze. In many cases that means using the 32-bit driver on a 64 bit system. There are two separate Microsoft ODBC Administrators on a 64-bit Windows OS. While the location of the ODBC Administrators are different, the names of the executables are exactly the same!

• 64-bit Microsoft ODBC Administrator (C:\Windows\System32\odbcad32.exe)
• 32-bit Microsoft ODBC Administrator (C:\Windows\SysWOW64\odbcad32.exe)

Configure ODBC Session 🠈 Quick and dirty steps.

The Oracle MS ODBC Administrator (under the Oracle menu) runs the 64-bit version on a 64-bit OS.

 Start | Oracle | Configuration and Migration Tools | MS ODBC Administrator

The Microsoft OS Data Sources (ODBC) runs the 64-bit version:

 Start | All Programs | Control Panel | Administrative Tools | Data Sources (ODBC)

Installing Oracle 32-bit ODBC Driver (11g)

Seeing all the ODBC issues that have existed, Oracle has embraced the installation of the 32-bit client along side your 64-bit database or client software. Download and install the Oracle 11g 32-bit client with these options:

1. (x) Custom
2. (x) Skip software updates
3. Language: <Use Default>
4. Software Location
   Name: OraClient3211g_home1 (if prompted)
   Path: c:\app\oracle\product\11.2.0.3\client32 (for 32-bit)
   Path: c:\app\oracle\product\11.2.0.3\client64 (for 64-bit)
   
   It is important is to make these different from other Oracle software on this system so as not to conflict. Thus the client32 etc.
5. Components

    
     [x] Oracle ODBC Driver
     [x] Oracle Provider for OLE DB 
     [x] Oracle Provider for .NET (if used) 
   
   * Install
   

• Close

Post Install Comments

• Make sure to set the environment variable TNS_ADMIN to the directory where your tnsnames.ora file is.
• If you use the 32-bit Oracle recommends that you create a shortcut on your desktop and label it "32-Bit ODBC Admin" for future use. Target: C:\Windows\SysWOW64\odbcad32.exe
• In some environments "Add" does not work right in the ODBC Administrator. The work around is to use "Add" to create a bogus entry (with say the DSN name "X") then use "Configure" to create one with the DSN name you need. This fixes the dreaded ORA-12557 "protocol adapter not loadable" error.
• If you are still having Oracle ODBC issues this Oracle support page document is useful: 741033.1

APPENDIX

Configure ODBC Session

1. Download and install an Oracle client (ex: winx64_12102_client.zip) with ODBC.
   • This will install the corresponding Oracle ODBC driver.
   • Make sure to set the install path to use oracle not current user you are logged in as!
   • Install using the Administrator option will give you useful debugging tools.
2. Create a tnsnames.ora file and entry for your database.
   Example: C:\app\oracle\product\12.1.0.2\client_1\network\admin\tnsnames.ora
3. Set the TNS_ADMIN environment variable.
   • Run Env Var Editor (Win10): rundll32.exe sysdm.cpl,EditEnvironmentVariables
   • Select New.
   • Variable Name: TNS_ADMIN
   • Variable value: C:\app\oracle\product\12.1.0.2\client_1\network\admin
   • Select: OK
4. Launch the ODBC Admin tool and create an entry for your database.
   • Run: C:\Windows\System32\odbcad32.exe (this is the 64-bit ODBC)
   • Select System DSN tab then Add.
   • Select Oracle driver: Oracle in OraClient12Home1 then Finish.
   • Data Source Name: <Name Your Application Will Use for ODBC Database>
   • Description: Leave Blank
   • TNS Service Name: <Your tnsnames.ora Service Name>
   • UserID: <Your Oracle Database UserName>
   • Select OK to save.
5. In many ODBC Oracle drivers you can also Test Connection from here too.