R-SQL Invalid value from generic function ‘fetch’, class “try-error”, expected “data.frame”

Question

I am having a problem to fetch some data from database using ROracle. Everything works perfect (I am getting the data from different tables without any problem), but one of the tables throws an error:

 library(ROracle)
    con <- dbConnect(dbDriver("Oracle"),"xxx/x",username="user",password="pwd")
    spalten<- dbListFields(con, name="xyz", schema = "x") # i still get the name of the columns for this table
    rs <- dbSendQuery(con, "Select * From x.xyz") # no error
    data <- fetch(rs) # this line throws an error
    dbDisconnect(con)

Fehler in .valueClassTest(ans, "data.frame", "fetch") : invalid value from generic function ‘fetch’, class “try-error”, expected “data.frame”

I followed this question: on stackoverflow, and i selected the columns

rs <- dbSendQuery(con, "Select a From x.xyz")

but none of it worked and gave me the same error.

Any ideas what am I doing wrong?

P.S. I have checked the sql query in Oracle SQL Developer, and I do get the data table there

Update:

If anyone can help me to locate/query my Oracle error log, then perhaps I can find out what is actually happening on the database server with my troublesome query.

Is there any output in the Oracle error logs? What is the exact line which fails? Can you call dbSendQuery(...) without an assignment, without any error? — Tim Biegeleisen
the line which throws an error is data <- fetch(rs), calling dbSendQuery(...) comes without any error — Mal_a
I suspect that there is a problem with your database connection. What do you see when you type con in the R console after attempting to connect? Are there any fields which indicate whether or not the connection were made successfully? — Tim Biegeleisen
The connection is succesfull, and i am sure about it, i tried getting some other tables, and i fetched the data without any problem, thats whats wondering me...only this one table throws an error — Mal_a
Can you show us code for a query where you were able to get a result set? — Tim Biegeleisen

Tim Biegeleisen Tim Biegeleisen · Accepted Answer · 2016-02-19T07:36:36

This is for debugging purposes only. Try running your code in the following tryCatch construct. It will display all warnings and errors which are happening.

result <- tryCatch({
    con <- dbConnect(dbDriver("Oracle"),"xxx/x",username="user",password="pwd")
    spalten <- dbListFields(con, name="xyz", schema = "x")
    rs <- dbSendQuery(con, "Select * From x.xyz") # no error
    data <- fetch(rs) # this line throws an error
    dbDisconnect(con) 
}, warning = function(war) {
    print(paste("warning:  ",war))
}, error = function(err) {
    print(paste("error:  ",err))
})

print(paste("result =",result))

R-SQL Invalid value from generic function ‘fetch’, class “try-error”, expected “data.frame”

2 Answers