Installing Oracle Odbc Drivers

| 17-08-2021
[RAND-1-100] Comments

It is ridiculously difficult and ambiguous how you’re supposed to get the Oracle ODBC driver installed on your Windows machine. It’s certainly not a simple task. Well, since I figured it out today I thought I would document it.

  1. Install Oracle Odbc Drivers
  2. Install Oracle Odbc Driver Centos 7
  3. Installing Oracle Odbc Driver Windows 7
  4. Install Oracle Odbc Driver Ubuntu

It is ridiculously difficult and ambiguous how you’re supposed to get the Oracle ODBC driver installed on your Windows machine. It’s certainly not a simple task. Well, since I figured it out today I thought I would document it. Had Oracle ODBC drivers on it, you can skip to Installing Oracle 10g ‐ XP/ Vista on page 21 or Installing Oracle 10g ‐ Windows 7 on page 23. Backing Up To back up your current Oracle configuration: 1 Restart your computer. 2 Log on to the PC. Oracle ODBC driver on IBM AIX is certified with ODBC Driver Manager 2.3.4. To use ODBC with Oracle Database, you must also install the following package: Copy gcc 4.2.1 or later.

First you need to download all three of these files from oracle
1) instantclient-basic-win-x86-64-11.1.0.7.0
3) instantclient-sdk-win-x86-64-11.1.0.7.0
Extract the contents of all the above into a single directory called D:OracleOra11
That directory doesn’t exist and you’ll have to create it first.
Now with all the files copied in there, create the following windows environment variables:
1) OCI_LIB64 = C:OracleOra11
3) TNS_ADMIN = C:OracleOra11
Also, add “C:OracleOra11” to your PATH variable.
At this point you MUST reboot. No other way around it. Simply restarting the explorer task won’t do.
After the reboot, create the following directory: C:OracleOra11NetworkAdmin
Place your tnsnames.ora file inside this new directory.
Open a cmd prompt as administrator.
On the command prompt type the following command:
odbc_install.exe
You should receive a message that says: “Oracle ODBC Driver is installed successfully.”
The new ODBC driver is only in the ODBC 64-bit data sources.
Just remember that your tnsnames.ora file must contain the service you’re trying to connect to.

Using RStudio Professional Drivers

When working with databases on RStudio Desktop Pro and other RStudio professional products, it is strongly recommended to use the RStudio Professional Drivers. Not only these come with full support, but also they simplify the installation and configuration process is most cases, not requiring many of the steps detailed below.

Using other drivers

For Linux and MacOS, ODBC drivers should be compiled against unixODBC. Drivers compiled against iODBCmay also work, but are not fully supported.

After installation of the driver manager and driver, you will have toregister the driver in a odbcinst.ini file for it to appear in odbc::odbcListDrivers().

Microsoft Windows

Database Drivers

Odbc

Windows is bundled with ODBC libraries; however, drivers for each databaseneed to be installed separately. Windows ODBC drivers typically include aninstaller that must be run to install the drivers in the properlocations.

Administration

The ODBC Data Source Administratorapplication is used to manage ODBC data sources on Windows.

Apple MacOS

Installation

  1. Install homebrew to install database drivers easily on MacOS

  2. Install UnixODBC, which is required for all databases

  1. Install common DB drivers (optional)

Setting up database connections

See the section with the same name in the Linux section.

Linux Debian / Ubuntu

Installation

The apt-get command can be used to install databasedrivers easily on Linux distributions that support it, such as Debian and Ubuntu.

  1. Install UnixODBC, which is required for all databases
  1. Install common DB drivers (optional)

Setting up database connections

On MacOS and Linux, there are two separate text files that need to be edited.UnixODBC includes a command-line executable called odbcinst, which can be used toquery and modify the DSN files. However, these are plain text files youcan also edit by hand if desired.

There are two different files used to set up the DSN information:

Install oracle odbc drivers on linux

  • odbcinst.ini defines driver options

  • odbc.ini defines connection options

odbcinst.ini

This file contains the driver information, particularly the name of the driver library.Multiple drivers can be specified in the same file.

odbc.ini

Install Oracle Odbc Drivers

This file contains the connection information, particularly the username, password, databaseand host information. The Driver line corresponds to the driver defined inodbcinst.ini.

See also: unixODBC without the GUI for more information and examples.

Location

The DSN configuration files can be defined globally for all users of thesystem, often at/etc/odbc.ini or /opt/local/etc/odbc.ini. The file location depends onwhat option was used when compiling unixODBC; odbcinst -j can be used to findthe exact location. Alternatively, the ODBCSYSINI environment variable can beused to specify the location of the configuration files. Ex. ODBCSYSINI=~/ODBC

Install Oracle Odbc Driver Centos 7

A local DSN file can also be used with the files ~/.odbc.ini and ~/.odbcinst.ini.

Connecting to a Database in R

Databases can be connected by specifying a connection string directly, or withDSN configuration files.

Connection Strings

Installing Oracle Odbc Driver Windows 7

Pass the connection parameters as arguments to the dbConnect() function.

For database-specific settings, go to the Databases section in the menu and look for the page that matches the desired database type.

DSN Configuration files

ODBC configuration files are another option to specify connection parameters; theyallow you to use a Data Source Name (DSN) to make it easier to connect to a database.

Install Oracle Odbc Driver Ubuntu

For more information about how DSN can be used, check out the following articles in this site: