oracle connection in r

Question

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

Does it hang if you fetch two of the three tables? Or the same table twice? Or try doing a select with a small limit (eg 10 rows)? Or opening and closing the odbc connection between fetches? — Spacedman
– Spacedman, Commented Jun 16, 2017 at 11:08
yes I have tried opening and closing between connections this also hangs up, also tried opening two files opened no issue but when I try three freezes or hangs, I will write some code later to try and select the top 20 or so records from each tables — Nigel Griffin
– Nigel Griffin, Commented Jun 16, 2017 at 11:35

Antoine N. · Accepted Answer · 2017-06-19 11:43:44Z

2

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)

edited Jun 19, 2017 at 11:43

answered Jun 16, 2017 at 11:58

Antoine N.

1518 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Nigel Griffin Over a year ago

Hi Aneuraz, thank you, I have tried sqlQuery, does the same, will try and find a connection string for RJDBC and will give that ago

Nigel Griffin Over a year ago

Hiya Aneuraz, I have tried all above it seems to be Oracle is slow at connecting through RODBC, So please could you give me an example of your connection string for RJDBC IF possible

Antoine N. Over a year ago

please, see my edited answer for an example @NigelGriffin.

Nigel Griffin Over a year ago

Hi Aneurezm I have tried the connection for the RJDC, below is my code I use but get the following error: Error in .jfindClass(as.character(driverClass)[1]) : class not found

Nigel Griffin Over a year ago

the code I used: library(RJDBC) library(RODBC) driverClass="oracle.jdbc.OracleDriver" classPath="C:/instantclient_11_2/ojdbc6‌.jar" connectPath="jdbc:oracle:thin:@//<xxxxxx>:<xxxx>/<xxxxx>" jdbcDriver <- RJDBC::JDBC(driverClass, classPath) jdbcConnection <- RJDBC::dbConnect(jdbcDriver,xxxx, xxxxx)

|

Collectives™ on Stack Overflow

oracle connection in r

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related