Error when writing Date columns to Oracle DB via odbc

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:

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.

#> [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)

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.

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.

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

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