How to Use R Language to Connect with an ORACLE Database

Unixodbc

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

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:

   1. For odbc.ini ...

1. [ORACLE2]
2. Driver = ORACLE2
3. ServerName = 143.93.91.33:1521/xe
4. UserID = your_username
5. Password = xxx
6. METADATA_ID = 0
7. ENABLE_USER_CATALOG = 1
8. ENABLE_SYNONYMS = 1
   1. For odbcinst.ini ...
9. [ORACLE2]
10. Description = oracle driver
11. Driver = /home/sven/instantclient_11_2/libsqora.so.11.1
12. DontDLClose = 1
13. FileUsage = 1
14. UsageCount = 1
    1. Notes: 'Driver' points at the file 'libsqora.so.11.1' in the Instant-client location.

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

The R language

1. 
   Obtain a package called 'RODBC' in order 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:

   1. install.packages(packagename.tar.gz)
3. 
   After the package installation you can create a channel:

   1. ch=odbcConnect("ORACLE2")
4. Check the channel with odbcGetInfo(ch)
5. If the connection was successful, the console shows something like this:

1. odbcGetInfo(ch)
2. DBMS_Name DBMS_Ver Driver_ODBC_Ver
3. "Oracle" "10.02.0010" "03.52"
4. Data_Source_Name Driver_Name Driver_Ver
5. "ORACLE2" "SQORA32.DLL" "11.02.0001"
6. ODBC_Ver Server_Name
7. "03.52" "143.93.91.33:1521/xe"