Error when writing Date columns to Oracle DB via odbc

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)",