Error when writing Date columns to Oracle DB via odbc

mfherman · November 22, 2019, 2:04am

I'm struggling with writing a date column to an Oracle database using DBI and odbc. Below is the error I get. I am able to write data frames with character and numeric columns without a problem.

I think the issue with the date column may relate to what odbc does do the data types for Oracle database connections: https://github.com/r-dbi/odbc/blob/1476b25a62314d912b680f0fb7ce2047dadbc5f3/R/DataTypes.R#L230

But it is a little outside my depth to figure out how to make this work. Does any have experience successfully writing to a date field to an Oracle DB using odbc and DBI?

My con object below is an Oracle database connection, but I've omitted my connection specifications from the reprex.

DBI::dbGetInfo(con)$dbms.name
#> [1] "Oracle"
df <- data.frame(DATE = seq(as.Date("2019-01-01"), as.Date("2019-12-31"), by = 1))
DBI::dbWriteTable(con, "TEST_DATE", df, overwrite = TRUE)
#> Error in result_insert_dataframe(rs@ptr, values): nanodbc/nanodbc.cpp:1587: 22003: [Oracle][ODBC]Numeric value out of range.

^{Created on 2019-11-21 by the reprex package (v0.3.0)}

andresrcs · November 22, 2019, 2:49am

I have no experience with Oracle databases but based on postgresql behavior, you could try using a posixct format and if the table structure already exist in the database, it is usually easier to pass the date as character string, letting the driver do the conversion.

mfherman · November 22, 2019, 2:37pm

Thanks for the input, but I still run into the same error if the database table already exists with the correct data types or if I'm creating a new table.

cderv · November 24, 2019, 4:57pm

I already encountered such difficulties with Date and DateTime types with oracle DB.
I think there is a missing feature but still not sure that R types are correctly handled to odbc data type conversion then to oracle datatype through an ODBC driver for oracle is really working well.

For writing to Oracle Database,I rely on ROracle mainly which uses OCI driver directly - not odbc.

It is difficult to make a reproductible test with such DB but I have one at work - I'll try again.

This code is part of my thinking about the issues

github.com

r-dbi/odbc/blob/e0b2f4944b1d07342cd2fc3c24d0791f59fdb3c0/R/DataTypes.R#L230


    double = "DOUBLE",
    character = varchar(obj),
    logical = "BIT",
    list = varchar(obj),
    stop("Unsupported type", call. = FALSE)
  )
}




#' @export
odbcDataType.Oracle <- function(con, obj, ...) {
  switch_type(obj,
     factor = "VARCHAR(255)",


     # No native oracle type for time
     time = "VARCHAR(255)",


     # There is a native type for date, but the default input format may not be
     # ISO 8601, it is determined by NLS_DATE_FORMAT or derived from
     # NLS_TERRITORY, so use character as a fallback.
     date = "VARCHAR(255)",

mfherman · December 15, 2019, 4:57pm

This topic was automatically closed 21 days after the last reply. New replies are no longer allowed.