How to load an Oracle database into R?

Question

I have access to an Oracle database, but have always used this data in the R using the PL/SQL to extract the data in csv. But now I would like to connect my Oracle database on itself RStudio.

After some research, I found some solutions, but I still couldn’t make them work.

install.packages("RODBC")

library(RODBC)

Conectar_Oracle = odbcDriverConnect("driver={Microsoft ODBC for Oracle};
                       server='123.456.7.89';
                       database='banco01';
                       uid='usuario';
                       pwd='senha'")

But the following error message came:

[RODBC] ERROR: state IM002, code 0, message [Microsoft][ODBC Driver Manager] Nome da fonte de dados não encontrado e nenhum driver padrão especificado

In some forums they say you need to install the Oracle connector, in others it says that it is possible to connect with the bank directly. But I couldn’t make any of those options work.

Can you help me? Where am I going wrong? What should I do?

Answer 1

For me what worked was to use the RJDBC.

The arrangement I’m proposing uses two files in the folder. One with the jar driver and other text file to hide the source code access credentials.

The first file can be downloaded here. Let’s say the second file was called config.oracle. Its content is similar to this:

jdbc:oracle:thin:@//{dns.do.servidor.br}:1521/nome.do.serviço.br
NOME_DO_USUARIO
SENHA_DO_USUARIO

And then you can use these files to create the connection with

driver <- RJDBC::JDBC("oracle.jdbc.OracleDriver", "ojdbc8.jar")
configs <- readLines('config.oracle')
conexao <- RJDBC::dbConnect(driver, configs[1], configs[2], configs[3])

And then just use the object conexao to make the query at the bank:

tabela <- RJDBC::dbGetQuery(conexao, "SELECT * FROM TABELA")

Editing

Below is how the object of driver

driver <- RJDBC::JDBC("oracle.jdbc.OracleDriver", "ojdbc8.jar")
class(driver)
[1] "JDBCDriver"
attr(,"package")
[1] "RJDBC"

str(driver)
Formal class 'JDBCDriver' [package "RJDBC"] with 2 slots
  ..@ identifier.quote: chr NA
  ..@ jdrv            :Formal class 'jobjRef' [package "rJava"] with 2 slots
  .. .. ..@ jobj  :<externalptr> 
  .. .. ..@ jclass: chr "oracle/jdbc/OracleDriver"