[SOLVED] Usage of ROracle generates Error in .oci.fetch(res, as.integer(n)) : ORA-01805:

Usage of ROracle generates Error in .oci.fetch(res, as.integer(n)) : ORA-01805:

I use ROracle to enhance data reading from Oracle to R. PC and OS specs: i5, 8Gb, Win7 64bit, Oracle 12c (client 64bit), R (64 bit, 3.2.2), Rstudio

Current code as it is:

>install.packages('ROracle_1.1-12.zip', repos = NULL)
>library('ROracle')
>drv <- dbDriver("Oracle")
>con <- dbConnect(drv, "USER", "PASSWORD", dbname='DB')
>x<-dbSendQuery(con, "SELECT * FROM MY_TABLE")
>y<-fetch(x)

But some error has appeared:

Error in .oci.fetch(res, as.integer(n)) : ORA-01805: ....

The point is that the error is not permanent. Somitemes it arises, sometimes it doesnt and the script is running Ok. I would be very grateful for your help with such a case.

Solution

May be you have different TZ (server\client) try

dbGetQuery(con,"SELECT SESSIONTIMEZONE,DBTIMEZONE FROM dual")

SESSIONTIMEZONE DBTIMEZONE

1 +03:00 +04:00

if you have different values change TZ

dbSendQuery(con,"ALTER SESSION SET TIME_ZONE = '+4:0'")
dbCommit(conn = con)

dbGetQuery(con,"SELECT SESSIONTIMEZONE,DBTIMEZONE FROM dual")

SESSIONTIMEZONE DBTIMEZONE

1 +04:00 +04:00

May be it can help you