How can I check if the connection is valid with the ROracle database?

# database.R
# Database options
options(user_db = Sys.getenv("user_db"))
options(pass_db = Sys.getenv("pass_db"))
options(host_db = Sys.getenv("host_db"))
options(port_db = Sys.getenv("port_db"))
options(name_db = Sys.getenv("name_db"))

drv <- dbDriver(drvName = "Oracle")

# Function that connect on db
connection <- function() {

  connect.string <- paste("(DESCRIPTION=",
                          "(ADDRESS=(PROTOCOL=tcp)(HOST=", options()$host_db, ")(PORT=", options()$port_db, "))",
                          "(CONNECT_DATA=(SERVICE_NAME=", options()$name_db, ")))", sep = "")

  pool <- dbPool(drv, dbname = connect.string, user = options()$user_db, password = options()$pass_db,
                 minSize = 10,
                 maxSize = Inf,    # this could have been omitted since it's the default
                 idleTimeout = 3600000 ) # one hour)
  return(pool)
}


shiny::onStop(function(){
  print("call pool stop")
  poolClose(pool)
})

CheckDatabase <- function () {
  cons <- dbListConnections(drv)
  if (length(cons) > 0) {
    for (con in cons) {
      tryCatch({
        dbGetQuery(con, "SELECT idt FROM schema.table WHERE ROWNUM <= 4")
        break
      }, error=function(cond) {
        con <- connection()
        return (con)

      },warning = function(cond){
        con <- connection()
        return (con)

      })
    }


  } else {
    con <- connection()
  }
  return(con)
}

# server.R 
source('database.R')

con <- CheckDatabase()

df <- tbl(CheckDatabase(), sql('SELECT * FROM schema.table))

If I don't take a while to perform the query, or use the checkDatabase() function, it works until I'm inactive for a while, then I get the error.
After a certain time, about 10 minutes or less, when I go to make a query in the DB, I receive:

ORA-03114: not connected to ORACLE 

How can I validate the connection?
Because I already tried like this:
dbIsValid(con), but I get that it's an oracle database connection and it doesn't work, like this:

 unable to find an inherited method for function ‘dbIsValid’ for signature ‘"OraConnection"’ 

This topic was automatically closed 54 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.