Cannot insert multiple rows with a date or timestamp field - Oracle

[Mosk915]

Issue Description and Expected Result

When I try to insert a data frame that contains either a date or timestamp field into a table, using either an insert statement or dbWriteTable, I get an error if the data frame has more than one row.

Database

Oracle Database 19c Enterprise Edition

Reproducible Example

options(digits.secs = 6)

df <- data.frame(DATETIME = c(Sys.time(), Sys.time()), stringsAsFactors = FALSE)

library(odbc)

# odbc connection
con <- DBI::dbConnect(odbc::odbc(),
                      driver = "Oracle18",
                      uid = "username",
                      pwd = "password",
                      dbq = "database",
                      timezone = Sys.timezone(),
                      timezone_out = Sys.timezone())

DBI::dbExecute(con, "create table test_table (datetime timestamp(6))")

# insert data
DBI::dbExecute(con, "insert into test_table (datetime) values (:1)", params = df)
# Error in result_bind(res@ptr, as.list(params), batch_rows) : 
#   nanodbc/nanodbc.cpp:1617: 00000: [Oracle][ODBC]Numeric value out of range.
# [Oracle][ODBC]General error. ut of range. 

DBI::dbExecute(con, "drop table test_table")

dbWriteTable(conn = con, name = "TEST_TABLE", value = df)
# Error in result_bind(res@ptr, as.list(params), batch_rows) : 
#   nanodbc/nanodbc.cpp:1617: 00000: [Oracle][ODBC]Numeric value out of range.
# [Oracle][ODBC]General error. ut of range. 

DBI::dbExecute(con, "drop table test_table")

Session Info

devtools::session_info()
─ Session info ───────────────────────────────────────────────────────────────────────────────────────────
 setting  value                       
 version  R version 3.6.0 (2019-04-26)
 os       Red Hat Enterprise Linux    
 system   x86_64, linux-gnu           
 ui       RStudio                     
 language (EN)                        
 collate  en_US.UTF-8                 
 ctype    en_US.UTF-8                 
 tz       America/New_York            
 date     2020-09-19                  

─ Packages ───────────────────────────────────────────────────────────────────────────────────────────────
 package     * version  date       lib source        
 assertthat    0.2.1    2019-03-21 [3] CRAN (R 3.6.0)
 backports     1.1.6    2020-04-05 [3] CRAN (R 3.6.0)
 bit           1.1-15.2 2020-02-10 [3] CRAN (R 3.6.0)
 bit64         0.9-7    2017-05-08 [3] CRAN (R 3.6.0)
 blob          1.2.1    2020-01-20 [3] CRAN (R 3.6.0)
 callr         3.4.3    2020-03-28 [3] CRAN (R 3.6.0)
 cli           2.0.2    2020-02-28 [3] CRAN (R 3.6.0)
 crayon        1.3.4    2017-09-16 [3] CRAN (R 3.6.0)
 DBI         * 1.1.0    2019-12-15 [3] CRAN (R 3.6.0)
 desc          1.2.0    2018-05-01 [3] CRAN (R 3.6.0)
 devtools      2.3.0    2020-04-10 [3] CRAN (R 3.6.0)
 digest        0.6.25   2020-02-23 [3] CRAN (R 3.6.0)
 ellipsis      0.3.0    2019-09-20 [3] CRAN (R 3.6.0)
 evaluate      0.14     2019-05-28 [3] CRAN (R 3.6.0)
 fansi         0.4.1    2020-01-08 [3] CRAN (R 3.6.0)
 fs            1.4.1    2020-04-04 [3] CRAN (R 3.6.0)
 glue          1.4.0    2020-04-03 [3] CRAN (R 3.6.0)
 hms           0.5.3    2020-01-08 [3] CRAN (R 3.6.0)
 htmltools     0.4.0    2019-10-04 [3] CRAN (R 3.6.0)
 knitr         1.28     2020-02-06 [3] CRAN (R 3.6.0)
 magrittr      1.5      2014-11-22 [3] CRAN (R 3.6.0)
 memoise       1.1.0    2017-04-21 [3] CRAN (R 3.6.0)
 odbc        * 1.2.2    2020-01-10 [3] CRAN (R 3.6.0)
 pkgbuild      1.0.6    2019-10-09 [3] CRAN (R 3.6.0)
 pkgconfig     2.0.3    2019-09-22 [3] CRAN (R 3.6.0)
 pkgload       1.0.2    2018-10-29 [3] CRAN (R 3.6.0)
 prettyunits   1.1.1    2020-01-24 [3] CRAN (R 3.6.0)
 processx      3.4.2    2020-02-09 [3] CRAN (R 3.6.0)
 ps            1.3.2    2020-02-13 [3] CRAN (R 3.6.0)
 R6            2.4.1    2019-11-12 [3] CRAN (R 3.6.0)
 Rcpp          1.0.4.6  2020-04-09 [3] CRAN (R 3.6.0)
 remotes       2.1.1    2020-02-15 [3] CRAN (R 3.6.0)
 rlang         0.4.5    2020-03-01 [3] CRAN (R 3.6.0)
 rmarkdown     2.1      2020-01-20 [3] CRAN (R 3.6.0)
 ROracle     * 1.3-1    2016-10-26 [3] CRAN (R 3.6.0)
 rprojroot     1.3-2    2018-01-03 [3] CRAN (R 3.6.0)
 rstudioapi    0.11     2020-02-07 [3] CRAN (R 3.6.0)
 sessioninfo   1.1.1    2018-11-05 [3] CRAN (R 3.6.0)
 testthat      2.3.2    2020-03-02 [3] CRAN (R 3.6.0)
 usethis       1.6.0    2020-04-09 [3] CRAN (R 3.6.0)
 vctrs         0.2.4    2020-03-10 [3] CRAN (R 3.6.0)
 withr         2.1.2    2018-03-15 [3] CRAN (R 3.6.0)
 xfun          0.13     2020-04-13 [3] CRAN (R 3.6.0)
 yaml          2.2.1    2020-02-01 [3] CRAN (R 3.6.0

[Mosk915]

Setting options(odbc.batch_rows = 1) fixes this issue. I'm not sure why though since there was no issue inserting multiple rows when there are no date columns.

[Mosk915]

Didn’t mean to close. Although setting batch_rows to 1 fixes the issue, it’s not great if it is significantly slower.

[Mosk915]

Closing again as it is the same as #350

[Mosk915]

Reopening since the other issues where this was discussed (#349 and #350) were closed without resolution.

[jimhester]

Should now be resolved by 65870d3