`copy_to` fails in Oracle 11g

[ghost]

@dmvianna commented on Sep 24, 2018, 2:49 AM UTC:

Please briefly describe your problem and what output you expect. If you have a question, please don't use this form. Instead, ask on https://stackoverflow.com/ or https://community.rstudio.com/.

Please include a minimal reproducible example (AKA a reprex). If you've never heard of a reprex before, start by reading https://www.tidyverse.org/help/#reprex.

---

Brief description of the problem

I am able to query Oracle fine, but I am unable to write data to it using the same connection. It seems ROracle does not have a function dbBegin dbplyr expects. I checked, it isn't there.

drv <- DBI::dbDriver("Oracle")
con <- dbConnect(drv, username = cred$username,
     password = cred$password, dbname = cred$cs)

copy_to( con
        , df
        , 'data_range')

gives me

Error in (function (classes, fdef, mtable) : unable to find an inherited method for function ‘dbBegin’ for signature ‘"OraConnection"’
Traceback:

1\. copy_to(con, df, "data_range")
2\. copy_to.DBIConnection(con, dfs, "feeder_volt_range")
3\. copy_to(dbplyr::src_dbi(dest, auto_disconnect = FALSE), df = df, 
 .     name = name, overwrite = overwrite, ...)
4\. copy_to.src_sql(dbplyr::src_dbi(dest, auto_disconnect = FALSE), 
 .     df = df, name = name, overwrite = overwrite, ...)
5\. db_copy_to(dest$con, name, df, overwrite = overwrite, types = types, 
 .     temporary = temporary, unique_indexes = unique_indexes, indexes = indexes, 
 .     analyze = analyze, ...)
6\. db_copy_to.DBIConnection(dest$con, name, df, overwrite = overwrite, 
 .     types = types, temporary = temporary, unique_indexes = unique_indexes, 
 .     indexes = indexes, analyze = analyze, ...)
7\. db_begin(con)
8\. db_begin.DBIConnection(con)
9\. dbBegin(con)
10\. (function (classes, fdef, mtable) 
  . {
  .     methods <- .findInheritedMethods(classes, fdef, mtable)
  .     if (length(methods) == 1L) 
  .         return(methods[[1L]])
  .     else if (length(methods) == 0L) {
  .         cnames <- paste0("\"", vapply(classes, as.character, 
  .             ""), "\"", collapse = ", ")
  .         stop(gettextf("unable to find an inherited method for function %s for signature %s", 
  .             sQuote(fdef@generic), sQuote(cnames)), domain = NA)
  .     }
  .     else stop("Internal error in finding inherited methods; didn't return a unique method", 
  .         domain = NA)
  . })(list(structure("OraConnection", package = "ROracle")), new("standardGeneric", 
  .     .Data = function (conn, ...) 
  .     standardGeneric("dbBegin"), generic = structure("dbBegin", package = "DBI"), 
  .     package = "DBI", group = list(), valueClass = character(0), 
  .     signature = "conn", default = NULL, skeleton = (function (conn, 
  .         ...) 
  .     stop("invalid call in method dispatch to 'dbBegin' (no default method)", 
  .         domain = NA))(conn, ...)), <environment>)
11\. stop(gettextf("unable to find an inherited method for function %s for signature %s", 
  .     sQuote(fdef@generic), sQuote(cnames)), domain = NA)

This issue was moved by batpigandme from tidyverse/dplyr#3852.

[colearendt]

Related failure on Oracle (12c) of overwrite = TRUE with the odbc package.

library(odbc)
library(DBI)
library(config)
library(dplyr)

cfg <- config::get(file = "/wrk/db-test-config.yml")
con <- do.call(dbConnect, cfg$oracle)
mytbl <- copy_to(con, iris, overwrite = TRUE)
#> Error: <SQL> 'DROP TABLE IF EXISTS iris'
#>   nanodbc/nanodbc.cpp:1587: HY000: ORA-00933: SQL command not properly ended
#> 

Note append = TRUE does not seem to be passed through either.

library(odbc)
library(DBI)
library(config)
library(dplyr)

cfg <- config::get(file = "/wrk/db-test-config.yml")
con <- do.call(dbConnect, cfg$oracle)
mytbl <- copy_to(con, iris, append = TRUE)
#> Error: Table "iris" exists in database, and both overwrite and append are FALSE

^{Created on 2018-10-10 by the reprex package (v0.2.1)}

^{Created on 2018-10-10 by the reprex package (v0.2.1)}

[edgararuiz-zz]

@colearendt - That issue should be solved by https://github.com/tidyverse/dbplyr/pull/85

[hadley]

Duplicate of #120