[Oracle] Error when writing/appending more than 1024 rows to table

[vivas89]

Hi Team,

One of our customers surfaced this issue using our Oracle odbc driver and the odbc and DBI packages. I spent some time trying to successfully reproduce. It looks like an issue with the latest version of odbc as 1.4.2 seems to work okay.

Issue: https://rstudioide.zendesk.com/agent/tickets/114557

With the following latest stable versions:

> packageVersion("odbc")
[1] ‘1.5.0’
> packageVersion("DBI")
[1] ‘1.2.3’

> odbc::dbGetInfo(con)
...
[1] "Oracle"
$db.version
[1] "12.02.0010"
$odbc.version
[1] "03.52"
$driver.version
[1] "2.0.7.1009"
$odbcdriver.version
[1] "03.80"
...

The customer provided reproducible code, which is available in the Zendesk ticket.
Here's the gist of it from my testing:

Non-working code

con <- dbConnect(drv = odbc::odbc(), dsn = "oracle")

DBI::dbExecute(con, "
  CREATE TABLE TEST_TABELL (
    PERIODE_ID      VARCHAR2(6),
    VERDI           NUMBER,
    TEST_ID         VARCHAR2(100),
    SYS_JOBB_ID     VARCHAR2(100),
    TEST_VARIANT    VARCHAR2(10),
    PAKKEVERSJON    VARCHAR2(20),
    SYS_LASTET_DATO DATE
  )"
)

TEST_TABELL <- tibble::tibble(
  PERIODE_ID      = seq(1, 1025) |> stringr::str_pad(6, pad = "0"),
  VERDI           = seq(1, 1025),
  test_id         = "test",
  sys_jobb_id     = "test_jobb",
  test_variant    = "1",
  pakkeversjon    = "1.0.0",
  sys_lastet_dato = lubridate::today()
) |>
  dplyr::rename_with(stringr::str_to_upper)

  DBI::dbAppendTable(
  con,
  name = "TEST_TABELL",
  value = TEST_TABELL
  )

Output from running the last DBI::dbAppendTable command yields the following error:

>   DBI::dbAppendTable(
+   con,
+   name = "TEST_TABELL",
+   value = TEST_TABELL
+   )
Error: nanodbc/nanodbc.cpp:1783: 00000
[RStudio][OracleOCI] (3000) Oracle Caller Interface: ORA-01861: literal does not match format string

No rows in table:

> row_count <- DBI::dbGetQuery(con, "SELECT COUNT(*) FROM TEST_TABELL")[[1]]
> row_count
[1] 0

Working code

I just changed the following rows from 1025 to 1024 from the code example above.

TEST_TABELL <- tibble::tibble(
  PERIODE_ID      = seq(1, 1024) |> stringr::str_pad(6, pad = "0"),
  VERDI           = seq(1, 1024),
  ...

Code produces no output and I can see the rows added to table:

> row_count <- DBI::dbGetQuery(con, "SELECT COUNT(*) FROM TEST_TABELL")[[1]]
> row_count
[1] 1024

---

Working older odbc verision

If I use an older odbc package with the latest DBI package, the original working code is successful:

> packageVersion("odbc")
[1] ‘1.4.2’
> packageVersion("DBI")
[1] ‘1.2.3’

Using 1025 rows to append to table:

TEST_TABELL <- tibble::tibble(
  PERIODE_ID      = seq(1, 1025) |> stringr::str_pad(6, pad = "0"),
  VERDI           = seq(1, 1025),
  ...

[detule]

Hi there:

Thanks for the report. Do you have the ability to test with the development version of the package / master branch of this repository? It may be the case that this was fixed in #810

Thanks!