Geek Answers Handbook

How to connect to Oracle database in R?

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

How to create a DSN for my Oracle database?

Any help would be greatly appreciated. Thanks,

+8
oracle r
source share
4 answers

I am not familiar with "R", however:

Oracle ODBC DSN is usually configured to

+4
source share

Here are the instructions that I developed for our site:

  • Install Oracle Instant Client. Files to extract: 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. Instant Instant Client and ODBC Instant Client databases must be unpacked into one directory. For example, C: \ Oracle \ instantclient_11_1.

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

  1. Add the location of the extracted files to the path environment variable (for example, C: \ oracle \ instantclient_11_1) a. Right-click "My Computer" on the desktop, click "Manage." b. Right-click Computer Management (Local), click Properties. from. On the Advanced tab, click the Options button in the Environment Variables section. e. Locate the Path entry in the System Variables section and click the Edit button. e. Add the path to the end in the Variable Value field. Note that items are separated by a semicolon. e. Click OK to confirm the changes. Close the Computer Management window.

  2. Double-click the odbc_install.exe file in the Instant client directory.

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

  4. Add data source for oracle database. but. Click the Add button b. Select "Oracle in instantclient11_1" and click "Finish." from. In the Oracle ODBC Driver Configuration dialog box, enter the following: Data Source Name: DSN Description: Roicel (or something else) TNS Service Name: SERVER: 1521 / DSN - change this User ID: your oracle username e. Click the Check button compound". You will be asked to enter a password, and if all goes well, you will receive a "Connection successful" message.

Then you can create a channel in R with:

odbcConnect(dsn, uid = uid, pwd = pwd, readOnly = TRUE)
+8
source share

I found that there are some problems connecting to Oracle with R. It may just be the implication and settings that we have in my company, but here is what I did to get it working.

 library(RODBC) ch=odbcConnect("<AliasForYourDatabase>",pwd = "xxxxxxxx", believeNRows=FALSE) odbcGetInfo(ch)

The critical step for me was 'believeNRows=FALSE' , otherwise I got the following error when I sent the request:

"Error in .Call (C_RODBCFetchRows, attr (channel," handle_ptr "), max, buffsize,: negative vector lengths are not allowed"

This is because Oracle does not provide the actual number of rows back to the connection.

+1
source share

In case anyone else comes across this old question and needs help connecting Oracle and R, this PDF file fully explains the process and provides the necessary download information.

{http://cran.fhcrc.org/web/packages/RODM/RODM.pdf}

0
source share

More articles:


All Articles