copy_to still fails with ROracle driver

[m-sostero]

The native ROracle driver (drv = dbDriver("Oracle")) doesn't allow to write tables on the database.

A similar issue described was described in #167 (linked to #120, which was ostensibly solved), now closed.

Minimum working example (credentials and names <redacted>):

library(DBI)
library(dbplyr)
library(ROracle)

con_roracle <- DBI::dbConnect(
    drv = dbDriver("Oracle"),
    dbname = "<DSN>",
    username = "<UID>",
    password = "<PWD>"
)

> con_roracle 
User name:             <UID> 
Connect string:        <DSN>
Server version:        19.0.0.0.0 
Server type:           Oracle RDBMS 
Results processed:     5 
OCI prefetch:          FALSE 
Bulk read:             1000 
Bulk write:            1000 
Statement cache size:  0 
Open results:          0 

> copy_to(con_roracle , mtcars, "MTCARS")
Error in (function (classes, fdef, mtable) : 
unable to find an inherited method for function ‘dbBegin’ for signature ‘"OraConnection"’

Notice that:

• All other (read-only, SELECT) operations work fine with the ROracle driver.
• Write operations work when the connection is configured with the ODBC driver drv = odbc::odbc().

[hadley]

Can you please provide the results of traceback()?

[m-sostero]

Traceback:

> copy_to(con_oracle, mtcars, "MTCARS")
Error in (function (classes, fdef, mtable)  : 
  unable to find an inherited method for function ‘dbBegin’ for signature ‘"OraConnection"’
> traceback()
10: stop(gettextf("unable to find an inherited method for function %s for signature %s", 
        sQuote(fdef@generic), sQuote(cnames)), domain = NA)
9: (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)", 
    ...
8: dbBegin(con)
7: with_transaction(con, in_transaction, {
       table <- dplyr::db_write_table(con, table, types = types, 
           values = values, temporary = temporary, overwrite = overwrite)
       create_indexes(con, table, unique_indexes, unique = TRUE)
       create_indexes(con, table, indexes)
       if (analyze) 
           dbplyr_analyze(con, table)
   })
6: db_copy_to.DBIConnection(dest$con, name, df, overwrite = overwrite, 
       types = types, temporary = temporary, unique_indexes = unique_indexes, 
       indexes = indexes, analyze = analyze, in_transaction = in_transaction, 
       ...)
5: db_copy_to(dest$con, name, df, overwrite = overwrite, types = types, 
       temporary = temporary, unique_indexes = unique_indexes, indexes = indexes, 
       analyze = analyze, in_transaction = in_transaction, ...)
4: copy_to.src_sql(dbplyr::src_dbi(dest, auto_disconnect = FALSE), 
       df = df, name = name, overwrite = overwrite, ...)
3: copy_to(dbplyr::src_dbi(dest, auto_disconnect = FALSE), df = df, 
       name = name, overwrite = overwrite, ...)
2: copy_to.DBIConnection(con_oracle, mtcars, "MTCARS")
1: copy_to(con_oracle, mtcars, "MTCARS")

Session info:

> sessionInfo()
R version 4.1.2 (2021-11-01)
Platform: x86_64-pc-linux-gnu (64-bit)
Running under: Red Hat Enterprise Linux 8.5 (Ootpa)

Matrix products: default
BLAS/LAPACK: /usr/lib64/libopenblas-r0.3.12.so

locale:
 [1] LC_CTYPE=en_US.UTF-8       LC_NUMERIC=C               LC_TIME=en_US.UTF-8        LC_COLLATE=en_US.UTF-8    
 [5] LC_MONETARY=en_US.UTF-8    LC_MESSAGES=en_US.UTF-8    LC_PAPER=en_US.UTF-8       LC_NAME=C                 
 [9] LC_ADDRESS=C               LC_TELEPHONE=C             LC_MEASUREMENT=en_US.UTF-8 LC_IDENTIFICATION=C       

attached base packages:
[1] stats     graphics  grDevices utils     datasets  methods   base     

other attached packages:
 [1] ROracle_1.3-1      plotly_4.9.4.1     RColorBrewer_1.1-2 scales_1.1.1       lubridate_1.7.10   readxl_1.3.1      
 [7] odbc_1.3.2         DBI_1.1.1          dbplyr_2.1.1       forcats_0.5.1      stringr_1.4.0      dplyr_1.0.7       
[13] purrr_0.3.4        readr_2.1.2        tidyr_1.1.3        tibble_3.1.3       ggplot2_3.3.5      tidyverse_1.3.1   

loaded via a namespace (and not attached):
 [1] Rcpp_1.0.7        arrow_7.0.0       assertthat_0.2.1  digest_0.6.27     utf8_1.2.2        R6_2.5.0          cellranger_1.1.0 
 [8] backports_1.2.1   reprex_2.0.1      httr_1.4.2        pillar_1.6.2      rlang_0.4.11      lazyeval_0.2.2    rstudioapi_0.13  
[15] data.table_1.14.0 blob_1.2.2        htmlwidgets_1.5.3 bit_4.0.4         munsell_0.5.0     broom_0.7.9       compiler_4.1.2   
[22] modelr_0.1.8      pkgconfig_2.0.3   htmltools_0.5.1.1 tidyselect_1.1.1  fansi_0.5.0       viridisLite_0.4.0 crayon_1.4.1     
[29] tzdb_0.1.2        withr_2.4.3       grid_4.1.2        jsonlite_1.7.2    gtable_0.3.0      lifecycle_1.0.0   magrittr_2.0.1   
[36] cli_3.0.1         stringi_1.7.3     renv_0.14.0       fs_1.5.0          xml2_1.3.2        ellipsis_0.3.2    generics_0.1.0   
[43] vctrs_0.3.8       tools_4.1.2       bit64_4.0.5       glue_1.4.2        hms_1.1.0         colorspace_2.0-2  gt_0.3.1         
[50] rvest_1.0.1       haven_2.4.3      

[mgirlich]

The ROracle package does not implement the dbBegin() method. So, this is an issue with ROracle.

As a workaround you can use copy_to(..., in_transaction = FALSE).