How to Connect R to Oracle?

Question

I need to connect R to oracle and I have been unsuccessful so far. I downloaded two packages: RODBC & RODM.

This is the statement that I've been using:

DB <- odbcDriverConnect("DBIORES1",uid="mhala",pwd="XXXXXXX")

But I get this error:

Error in odbcDriverConnect("DBIORES1", uid = "mhalagan", pwd = "XXXXXXX") : 
  unused argument(s) (uid = "mhalagan", pwd = "XXXXXXX")

What information do I need to be able to connect to an oracle database? Am I using the correct package?

Answer 1

See the help page for odbcDriverConnect(). odbcDriverConnect() does not accept uid or pwd arguments. You probably meant to use odbcConnect() instead:

odbcConnect(dsn = "DBIORES1", uid = "mhala", pwd = "XXXXXXX")

In addition to the RODBC package, there is the RODM package, which I believe is specifically designed for Oracle databases and is further described here: http://www.oracle.com/technetwork/articles/datawarehouse/saternos-r-161569.html . I do not use Oracle databases, so cannot comment on advantages of the two packages.

Answer 2

RJDBC worked just fine for me. You just need to have Oracle-thin driver jar file and configure the connection like:

> install.packages("RJDBC")
> library(RJDBC)
> drv <- JDBC("oracle.jdbc.driver.OracleDriver","/path/to/driver/com/oracle/oracle-thin/11.2.0.1.0/oracle-thin-11.2.0.1.0.jar”)
> conn <- dbConnect(drv, "jdbc:oracle:thin:@database:port:schema”, “user”, “passwd”)

and then is ready to perform some queries.

JA.