How do I connect to an Oracle Database in R?

Question

I am trying to use the RODBC package to query data from an Oracle database using R. There seems to be a great deal of documentation on accessing MySQL databases from R, but not much information on Oracle databases.

How do I create a DSN for my Oracle Database?

Answer 1

Here are the instructions I developed for our site:

1. Install Oracle instant client. The files to extract are: instantclient-basic-win32-11.1.0.7.0.zip instantclient-odbc-win32-11.1.0.7.0.zip instantclient-sqlplus-win32-11.1.0.7.0.zip (optional)

Note: The Basic Instant Client and ODBC Instant Client packages must be unzipped into same directory. For example, C:\Oracle\instantclient_11_1.

Oracle download site: http://www.oracle.com/technology/software/tech/oci/instantclient/htdocs/winsoft.html

2. Add the location of the extracted files to the path environment variable (e.g. C:\oracle\instantclient_11_1) a. Right click "My Computer" on the desktop, click "Manage." b. Right click "Computer Management (Local)", click "Properties." c. On the advanced tab, click the Settings button under "Environment Variables." d. Find the "Path" entry under "System variables" and click the Edit button. e. Add the location path to the end of the "Variable Value" box. Note that items are separated by a semi-colon. f. Click OK to confirm changes. Close the Computer Management window.

3. Double click the odbc_install.exe file in the Instant client directory.

4. Open C:\WINDOWS\system32\odbcad32.exe

5. Add a data source for the oracle database. a. Click the Add button b. Select "Oracle in instantclient11_1" and click Finish. c. Enter the following in the Oracle ODBC Driver Configuration dialog: Data Source Name: DSN Description: Roacle (or whatever) TNS Service Name: SERVER:1521/DSN - Change this User ID: Your oracle user name d. Click the Test Connection button. You will be prompted for your password, and if everything went well, you will get a "Connection successful" message.

You can then create a channel in R with:

     odbcConnect(dsn, uid = uid, pwd = pwd, readOnly = TRUE)