Datetime in Oracle Database

[RodrigueJr]

Hi,

I'm using odbc to write table in Oracle , but I have some trouble with the datatype datetime (POSIXct).

I see here https://github.com/r-dbi/odbc/blob/master/R/DataTypes.R#L242 that datetime is not translate in Oracle.

Is there any fix for that or do you have some advice to use datetime with odbc?

Ps: It works with ROracle but we want to use only odbc (We have many database in our Organisation)

Reprex:

library(DBI)
library(dplyr)
con <- dbConnect(odbc::odbc(), "ORACLE_DSN") #I use a DSN to connect
res <- 
     mtcars %>%
     mutate(datevalue = Sys.time())
dbWriteTable(con, "DATETIME_TEST_ODBC", res, row.names = FALSE, overwrite = TRUE)

Error:
Unsupported type

Obdc V 1.2.0
DBI 1.0.0
dplyr 0.8.3

Thanks,

[Mosk915]

Has there been any update on this? I am having the same issue with inserting POSIXct values. Date values insert but not in the correct way.

> dbWriteTable(conn = conn, name = "TEST_TABLE", value = data.frame(TIME = Sys.time()))
Error: Column 'TIME' is of unsupported type: 'datetime'

> dbWriteTable(conn = conn, name = "TEST_TABLE", value = data.frame(TIME = Sys.Date()))

> dbGetQuery(conn = conn, "select * from test_table")
       TIME
1 25-SEP-20

> dbGetQuery(conn = conn, "select data_type from all_tab_cols where table_name = 'TEST_TABLE'")
  DATA_TYPE
1  VARCHAR2

This seems to be due to how the data type conversion is implemented for Oracle. Dates are converted to VARCHAR(255) for some reason instead of DATE and datetimes are unsupported instead of being converted to TIMESTAMP.