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:

   • 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.

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:

   • 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"