CODEWITHSUNDEEP
roracle-database
Nigel Griffin
Nigel Griffin

Reputation: 11

oracle connection in r

hi folks I can connect to oracle through R no problem using the following code:

library(RODBC)

channel <- odbcConnect(dsn = "xxxx", uid = "xxxx", pwd = "xxxx")
odbcGetInfo(channel)## CHECKS CONNECTION TO ORACLE


COMPANIES <- sqlFetch(channel, "COMPANIES")
COMPANIES_EQUIPMENT <-sqlFetch(channel, "COMPANIES_EQUIPMENT")
EQUIPMENT_SENSORS  <-sqlFetch(channel, "EQUIPMENT_SENSORS")


odbcClose(channel) ## CLOSES odbc CONNECTION

when I fetch the first data table "COMPANIES" , no issue but this means running the code just to fetch this data frame, the problem is that when I run the above code to fetch all 3 data frames: COMPANIES, COMPANIES_EQUIPMENT, EQUIPMENT_SENSORS my R script just hangs up, I have tried to run each fetch statement individually, and they all work but when run together my script just hangs up any ideas?

Not sure if problem is R, New laptop or Oracle, oracle seems ok as can connect no issue but is there a data limit maybe allowed etc...

I am using Oracle Instantclient 11.2 to connect my laptop windows 7 Professional to Oracle, RStudio Version 1.0.143

thanks

Nigel

Upvotes: 1

Views: 217

Answers (1)

Antoine N.
Antoine N.

Reputation: 151

Have you considered using sqlQuery instead of sqlFetch? 

COMPANIES <- sqlQuery(channel, "SELECT * FROM COMPANIES")

you might need to replace the * by the names of the variables.

I personnally use RJDBC to connect to Oracle: 

driverClass="oracle.jdbc.OracleDriver"
classPath="<PATH_TO_INSTANTCLIENT>/instantclient_12_1/ojdbc6‌​.jar"
connectPath="jdbc:oracle:thin:@//<HOST>:<PORT>/<DB_NAME>" 
jdbcDriver <- RJDBC::JDBC(driverClass, classPath)
jdbcConnection <- RJDBC::dbConnect(jdbcDriver, connectPath, dbuser, dbpass)

Upvotes: 2

Related Questions