Timestamps with ORACLE and odbc 1.5.0: Error Datetime field overflow resulting from invalid datetime

[desval]

After updating to odbc 1.5.0 I am experiencing issues when writing tables containing timestamps to an ORACLE version 19.00.0000 database. Interestingly, the issue arises only if the table is long enough. The issue disappears when reverting back to odbc 1.4.2

df <- data.frame(
  ts = rep(Sys.time(), 50000) # no error when using 500
  )

odbc::dbWriteTable(
  conn      = con,
  name      = "bug_test_timestamp",
  value     = df,
  overwrite = TRUE
)
#> Error: nanodbc/nanodbc.cpp:1783: 00000
#> [RStudio][Support] (40520) Datetime field overflow resulting from invalid datetime.

devtools::session_info()
#> ─ Session info ───────────────────────────────────────────────────────────────
#>  setting  value
#>  version  R version 4.4.1 (2024-06-14)
#>  os       Oracle Linux Server 8.9
#>  system   x86_64, linux-gnu
#>  ui       X11
#>  language (EN)
#>  collate  en_US.UTF-8
#>  ctype    en_US.UTF-8
#>  tz       Europe/Zurich
#>  date     2024-10-09
#>  pandoc   3.1.11 @ /usr/lib/rstudio-server/bin/quarto/bin/tools/x86_64/ (via rmarkdown)
#> 
#> ─ Packages ───────────────────────────────────────────────────────────────────
#>  ! package     * version    date (UTC) lib source
#>    bit           4.5.0      2024-09-20 [1] RSPM (R 4.4.1)
#>    bit64         4.5.2      2024-09-22 [1] RSPM (R 4.4.1)
#>  P blob          1.2.4      2023-03-17 [?] RSPM
#>  P cachem        1.1.0      2024-05-16 [?] RSPM (R 4.4.1)
#>  P cli           3.6.3      2024-06-21 [?] RSPM
#>  P config        0.3.2      2023-08-30 [?] RSPM (R 4.4.1)
#>  P DBI           1.2.3      2024-06-02 [?] RSPM
#>  P dbplyr        2.5.0      2024-03-19 [?] RSPM (R 4.4.1)
#>  P devtools      2.4.5      2022-10-11 [?] RSPM
#>    digest        0.6.37     2024-08-19 [1] RSPM (R 4.4.1)
#>  P dplyr         1.1.4      2023-11-17 [?] RSPM
#>  P ellipsis      0.3.2      2021-04-29 [?] RSPM (R 4.4.1)
#>    evaluate      1.0.0      2024-09-17 [1] RSPM (R 4.4.1)
#>  P fansi         1.0.6      2023-12-08 [?] RSPM
#>  P fastmap       1.2.0      2024-05-15 [?] RSPM (R 4.4.1)
#>  P fs            1.6.4      2024-04-25 [?] RSPM (R 4.4.1)
#>  P generics      0.1.3      2022-07-05 [?] RSPM
#>    glue          1.8.0      2024-09-30 [1] RSPM (R 4.4.1)
#>  P hms           1.1.3      2023-03-21 [?] RSPM
#>  P htmltools     0.5.8.1    2024-04-04 [?] RSPM
#>  P htmlwidgets   1.6.4      2023-12-06 [?] RSPM
#>  P httpuv        1.6.15     2024-03-26 [?] RSPM
#>  P janitor       2.2.0      2023-02-02 [?] RSPM (R 4.4.1)
#>  P knitr         1.48       2024-07-07 [?] RSPM (R 4.4.1)
#>  P later         1.3.2      2023-12-06 [?] RSPM
#>  P lifecycle     1.0.4      2023-11-07 [?] RSPM
#>  P lubridate     1.9.3      2023-09-27 [?] RSPM (R 4.4.1)
#>  P magrittr      2.0.3      2022-03-30 [?] RSPM
#>  P memoise       2.0.1      2021-11-26 [?] RSPM (R 4.4.1)
#>  P mime          0.12       2021-09-28 [?] RSPM (R 4.4.1)
#>  P miniUI        0.1.1.1    2018-05-18 [?] RSPM
#>  P odbc          1.5.0      2024-06-05 [?] RSPM
#>  P pillar        1.9.0      2023-03-22 [?] RSPM
#>  P pkgbuild      1.4.4      2024-03-17 [?] RSPM (R 4.4.1)
#>  P pkgconfig     2.0.3      2019-09-22 [?] RSPM
#>  P pkgload       1.4.0      2024-06-28 [?] RSPM (R 4.4.1)
#>  P profvis       0.4.0      2024-09-20 [?] RSPM
#>  P promises      1.3.0      2024-04-05 [?] RSPM
#>  P purrr         1.0.2      2023-08-10 [?] RSPM (R 4.4.1)
#>  P R6            2.5.1      2021-08-19 [?] RSPM
#>  P Rcpp          1.0.13     2024-07-17 [?] RSPM
#>  P remotes       2.5.0      2024-03-17 [?] RSPM
#>  P reprex        2.1.1      2024-07-06 [?] RSPM
#>  P rlang         1.1.4      2024-06-04 [?] RSPM
#>  P rmarkdown     2.28       2024-08-17 [?] RSPM
#>  P roxygen2      7.3.2      2024-06-28 [?] RSPM (R 4.4.1)
#>  P rstudioapi    0.16.0     2024-03-24 [?] RSPM
#>  P sessioninfo   1.2.2      2021-12-06 [?] RSPM
#>  P shiny         1.9.1      2024-08-01 [?] RSPM
#>  P snakecase     0.11.1     2023-08-27 [?] RSPM (R 4.4.1)
#>  P stringi       1.8.4      2024-05-06 [?] RSPM (R 4.4.1)
#>  P stringr       1.5.1      2023-11-14 [?] RSPM (R 4.4.1)
#>  P tibble        3.2.1      2023-03-20 [?] RSPM
#>  P tictoc        1.2.1      2024-03-18 [?] RSPM (R 4.4.1)
#>  P tidyr         1.3.1      2024-01-24 [?] RSPM (R 4.4.1)
#>  P tidyselect    1.2.1      2024-03-11 [?] RSPM
#>  P timechange    0.3.0      2024-01-18 [?] RSPM (R 4.4.1)
#>  P urlchecker    1.0.1      2021-11-30 [?] RSPM
#>  P usethis       3.0.0      2024-07-29 [?] RSPM
#>  P utf8          1.2.4      2023-10-22 [?] RSPM
#>  P vctrs         0.6.5      2023-12-01 [?] RSPM
#>  P withr         3.0.1      2024-07-31 [?] RSPM
#>    xfun          0.48       2024-10-03 [1] RSPM (R 4.4.1)
#>  P xml2          1.3.6      2023-12-04 [?] RSPM (R 4.4.1)
#>  P xtable        1.8-4      2019-04-21 [?] RSPM
#>  P yaml          2.3.10     2024-07-26 [?] RSPM (R 4.4.1)
#> 
#>  [1] /home/val/projects_tmp/odbc_bug/renv/library/linux-ol-8.9/R-4.4/x86_64-pc-linux-gnu
#>  [2] /home/val/.cache/R/renv/sandbox/linux-ol-8.9/R-4.4/x86_64-pc-linux-gnu/22bdb599
#>  [3] /opt/R/4.4.1/lib/R/library
#> 
#>  P ── Loaded and on-disk path mismatch.
#> 
#> ──────────────────────────────────────────────────────────────────────────────

^{Created on 2024-10-09 with reprex v2.1.1}

[detule]

Hi there:

Thanks for the report. Can you check the development/github version of the package? We recently did some work to attempt and rectify some issues with the oracle drivers and date/time/stamps fields. That change will make it into the next published version of the package ( 1.5.1 ).

I checked against Oracle 21, with both the OEM as well as the PRO drivers and I am unable to replicate the issue when using github/main. Hopefully the delta is that change ( and not the different DB version ).

Thanks!

[desval]

Hi, thanks for the quick reply.

Yes, installing the dev version with pak::pak("r-dbi/odbc") solves the issue.

Thank you.