Ini: How to Use R Language to Connect with an Oracle Database

This updated guide examines Ini: How to Use R Language to Connect with an Oracle Database and organizes the essential facts, background, and practical takeaways in clear American English.

Method 1

Instant Client + ODBC packages

1. Choose your OS (Linux 32) here: http://www.oracle.com/technetwork/database/features/instant-client/index-097480.html
2. Accept the License Agreement and agree to download:
   • 1. Instant Client Package - Basic
     2. Instant Client Package - ODBC
3. Unzip both packages in the same directory. For example, /home/sven/instantclient_11_2

Method 2

Unixodbc

1. Download unixodbc from here: http://www.unixodbc.org/download.html.
2. Install it.

Method 3

Edit odbc.ini & odbcinst.ini

1. Find the two.ini files. These are located in /etc/ (Ubuntu). Perhaps there are similar files in other locations; if so, it's recommended that you delete them.
2. Add a new entry like this:
   • For odbc.ini...

• [ORACLE2]
• Driver = ORACLE2
• ServerName = 143.93.91.33:1521/xe
• UserID = your_username
• Password = xxx
• METADATA_ID = 0
• ENABLE_USER_CATALOG = 1
• ENABLE_SYNONYMS = 1
  • For odbcinst.ini...
• [ORACLE2]
• Description = oracle driver
• Driver = /home/sven/instantclient_11_2/libsqora.so.11.1
• DontDLClose = 1
• FileUsage = 1
• UsageCount = 1
  • Notes: 'Driver' points at the file 'libsqora.so.11.1' in the Instant-client location.

Method 4

Add to your path

1. You may need to add 2 entries to your path (example):

   export OCI_LIB=/home/sven/instantclient_11_2

   export LD_LIBRARY_PATH=/home/sven/instantclient_11_2

Method 5

The R language

1. Obtain a package called 'RODBC' to connect with the database. Download it here: http://cran.r-project.org/web/packages/RODBC/index.html
2. Start R, switch to the location which contains the RODBC package and an enter the following R code:
   • install.packages(packagename.tar.gz)
3. After the package installation you can create a channel:
   • ch=odbcConnect("ORACLE2")
4. Check the channel with odbcGetInfo(ch)
5. If the connection was successful, the console shows something like this:

• odbcGetInfo(ch)
• DBMS_Name DBMS_Ver Driver_ODBC_Ver
• "Oracle" "10.02.0010" "03.52"
• Data_Source_Name Driver_Name Driver_Ver
• "ORACLE2" "SQORA32. DLL" "11.02.0001"
• ODBC_Ver Server_Name
• "03.52" "143.93.91.33:1521/xe"