copy_dm_to() issues on Oracle

[moodymudskipper]

I'm having some problems with Oracle whatever angle I choose. I suspect some of those are not Oracle specific.

The use case is to copy the content of a dm to a specific schema.

1. using the schema arg

  dm::copy_dm_to(
    connect_to_db(),
    dm,
    schema = "STORE_VT",
    temporary = FALSE
  )
#> Error in `abort_no_schemas_supported()`:
#> ! Currently schemas are not supported for a connection of class `Oracle`.
#> Backtrace:
#>     ?
#>  1. +-dm::copy_dm_to(connect_to_db(), dm, schema = "STORE_VT", temporary = FALSE)
#>  2.   +-dm:::repair_table_names_for_db(...)
#>  3.     +-dm:::abort_no_schemas_supported(con = con)
#>  4.       +-rlang::abort(error_txt_no_schemas_supported(dbms, con), class = dm_error_full("no_schemas_supported"))

2. table_names as a named character vector

table_names <- setNames(sprintf("STORE_VT.%s", names(dm)), names(dm))
dm::copy_dm_to(
  connect_to_db(),
  dm,
  table_names =  table_names,
  temporary = FALSE
)
#>   downloading data [==========================================================] 100% in  1s
#> Error in `pmap()`:
#> ℹ In index: 1.
#> Caused by error in `result_insert_dataframe()`:
#> ! nanodbc/nanodbc.cpp:1752: 00000: [RStudio][Support] (40465) String data truncated while performing conversion. 
#> Run `rlang::last_trace()` to see where the error occurred.

3. Using the documented formula :

I would expect it to be the same as above but this is different.
This creates the tables but doesn't populate them. They're a bit hard to remove properly too, maybe the code should have a safe cleanup on exit if no success ?

  dm::copy_dm_to(
    connect_to_db(),
    dm,
    table_names =  ~DBI::SQL(sprintf("STORE_VT.%s", .x)),
    temporary = FALSE
  )
#>   downloading data [=========================================================================>------------]  86% in  0s  downloading data [======================================================================================] 100% in  1s
#> Error in `pmap()`:
#> ℹ In index: 1.
#> Caused by error in `DBI::dbAppendTable()`:
#> ! dbExistsTable(conn, name) is not TRUE
#> Backtrace:
#>      ▆
#>   1. ├─dm::copy_dm_to(...)
#>   2. │ └─purrr::pwalk(...)
#>   3. │   └─purrr::pmap(.l, .f, ..., .progress = .progress)
#>   4. │     └─purrr:::pmap_("list", .l, .f, ..., .progress = .progress)
#>   5. │       ├─purrr:::with_indexed_errors(...)
#>   6. │       │ └─base::withCallingHandlers(...)
#>   7. │       ├─purrr:::call_with_cleanup(...)
#>   8. │       └─dm (local) .f(name = .l[[1L]][[i]], remote_name = .l[[2L]][[i]], ...)
#>   9. │         └─dm (local) f(...)
#>  10. │           └─dm:::db_append_table(...)
#>  11. │             ├─DBI::dbAppendTable(con, remote_table, table)
#>  12. │             └─DBI::dbAppendTable(con, remote_table, table)
#>  13. │               └─base::stopifnot(dbExistsTable(conn, name))
#>  14. │                 └─base::stop(simpleError(msg, call = if (p <- sys.parent(1L)) sys.call(p)))
#>  15. └─purrr (local) `<fn>`(`<smplErrr>`)
#>  16.   └─cli::cli_abort(...)
#>  17.     └─rlang::abort(...)

4. a vector of DBI::Id objects.

It says a vector in the doc, but these are not atomic so I think we might say list.

A first try doesn't work, but after inspection it's because the function is applied on the names vector as a whole, not individual elements, something I don't find intuitive and not very clearly documented:

  dm::copy_dm_to(
    connect_to_db(),
    dm,
    table_names =  ~DBI::Id(table = .x, schema = "STORE_VT"),
    temporary = FALSE
  )
#> Error in `set_names()`:
#> ! `x` must be a vector
#> Backtrace:
#>     ▆
#>  1. ├─dm::copy_dm_to(...)
#>  2. │ └─rlang::set_names(table_name_fun(src_names), src_names)
#>  3. └─rlang::abort(message = message)

A second try gives a different error :

  dm::copy_dm_to(
    connect_to_db(),
    dm,
    table_names = function(x) map(x, ~DBI::Id(table = .x, schema = "STORE_VT")),
    temporary = FALSE
  )
#>   downloading data [=================================================>--------]  86% in  0s  downloading data [==========================================================] 100% in  1s
#> Error in `map()`:
#> ℹ In index: 1.
#> Caused by error in `new_result()`:
#> ! nanodbc/nanodbc.cpp:1691: 00000: [RStudio][OracleOCI] (3000) Oracle Caller Interface: ORA-01918: user 'lds_D_LEM_KAMPAGNE_V' does not exist
#>  
#> Backtrace:
#>      ▆
#>   1. ├─dm::copy_dm_to(...)
#>   2. │ └─purrr::walk(...)
#>   3. │   └─purrr::map(.x, .f, ..., .progress = .progress)
#>   4. │     └─purrr:::map_("list", .x, .f, ..., .progress = .progress)
#>   5. │       ├─purrr:::with_indexed_errors(...)
#>   6. │       │ └─base::withCallingHandlers(...)
#>   7. │       ├─purrr:::call_with_cleanup(...)
#>   8. │       └─dm (local) .f(.x[[i]], ...)
#>   9. │         └─dm (local) f(...)
#>  10. │           ├─DBI::dbExecute(dest_con, .x, immediate = TRUE)
#>  11. │           └─DBI::dbExecute(dest_con, .x, immediate = TRUE)
#>  12. │             ├─DBI::dbSendStatement(conn, statement, ...)
#>  13. │             └─odbc::dbSendStatement(conn, statement, ...)
#>  14. │               └─odbc (local) .local(conn, statement, ...)
#>  15. │                 └─odbc:::OdbcResult(...)
#>  16. │                   └─odbc:::new_result(connection@ptr, statement, immediate)
#>  17. ├─base::stop(`<nndbc::_>`)
#>  18. └─purrr (local) `<fn>`(`<nndbc::_>`)
#>  19.   └─cli::cli_abort(...)
#>  20.     └─rlang::abort(...)

[krlmlr]

Oracle in Docker:

docker pull --platform linux:amd64 container-registry.oracle.com/database/free:latest

Documentation: https://container-registry.oracle.com/ords/f?p=113:4:117098920722595:::4:P4_REPOSITORY,AI_REPOSITORY,AI_REPOSITORY_NAME,P4_REPOSITORY_NAME,P4_EULA_ID,P4_BUSINESS_AREA_ID:1863,1863,Oracle%20Database%20Free,Oracle%20Database%20Free,1,0&cs=3GMsysVwQE0tYmEe_70uXaMrg4AZ3KpT7KRYRIs8ycazWRebFB68Ku5nwuRnRRfO9byNDuHJRBjUQ-qck-TkyPg