Error while inserting date column into oracle database using odbc package in R

General
, ,
#1

we are using odbc connection to connect target database (oracle database) and we need to insert a dataframe into target database. We are using dbBind() to insert data frame into database. Please find the sample data and code mentioned below.

Dataframe: Inputdata
  column1    column2    column3      date_column  column4
  <chr>      <chr>      <chr>        <chr>         <chr>
1 1111       6          fff          2015-11-01    MCA
2 2222       1          aaa          2006-12-02    NA
3 3333       2          bbb          2007-10-03    NA
4 4444       3          ccc          2008-01-04    NA
5 555        4          ddd          2007-11-05    NA
6 6666       5          eee          2010-11-06    BCH


R script :
Target1Conn<-dbConnect(odbc::odbc(), dsn = "TARGETDB", uid = "username", pwd = "password")
insert <- dbSendQuery(Target1Conn, paste0('insert into ', outputTableName, '(column1,column2,column3,date_column,column4) values(?,?,?,?,?)'))
dbBind(insert, list(InputData$column1,InputData$column2,InputData$column3,InputData$date_column,InputData$column4))"

Error:
Error in result_bind(res@ptr, as.list(params), batch_rows) :
  nanodbc/nanodbc.cpp:1617: 00000: [Oracle][ODBC][Ora]ORA-01861: literal does not match format string

My DataFrame has 1.3 million records and with the above approach after inserting 1024 records, the script is getting failed with error mentioned above.

if we ignore date column and insert data , we are able to insert data into database without any issues. Please let us know if any other solution is present.

#2

I’ve had some trouble getting Oracle and odbc/DBI to play nicely with dates (as others have discussed here too Error when writing Date columns to Oracle DB via odbc).

My most recent solution, which is not very elegant, is to write dates as characters to a database table and then copy the table to a new table on the database while converting to an Oracle date variable. I wrap all this database copying and pasting in an R function that uses dbQuery() so I don’t have to think about it, but it’s not pretty.

Very much open to better solutions if they exist!

1 Like
#3

Hi Matt,
Thanks for the reply. We will try the same solution.
May I know why odbc is unable to handle date column for oracle database.

#4

If you are trying to load into a table with a date field, I would try having the field in your input data be a date field, and not a char. If that would work, it would be easier than loading char to another table and converting again in database.

#5

Hi Nir,

we have tried to convert input data to date in data frame and tried inserting but still we are facing issue.

#6

Thanks, is the error different from the one posted here?

#7

also can you try odbcListColumns() so we can verify that it is a date and not a datetime, in terms of the target table ?

#8

@sachall2, my implementation of the write/copy/convert looks something like this:

write_db <- function (con, df, table,  ...)  {
    names(df) <- toupper(names(df))
    df <- mutate_if(df, lubridate::is.Date, ~as.POSIXct(as.character(.x)))
    col_class <- purrr::map_lgl(df, lubridate::is.POSIXt)
    col_dt <- names(which(col_class == TRUE))
    if (length(col_dt) > 0) {
        df[col_dt] <- purrr::map(df[col_dt], as.character)
        col_dt_new <- paste0(col_dt, "_NEW")
    }
    DBI::dbWriteTable(con, table, df, ...)
    if (length(col_dt) > 0) {
        add_cols <- glue::glue("ALTER TABLE {table} ADD {col_dt_new} DATE")
        update_cols <- glue::glue("UPDATE {table} SET {col_dt_new} = TO_TIMESTAMP({col_dt}, 'YYYY-MM-DD HH24:MI:SS')")
        drop_cols <- glue::glue("ALTER TABLE {table} DROP COLUMN {col_dt}")
        rename_cols <- glue::glue("ALTER TABLE {table} RENAME COLUMN {col_dt_new} to {col_dt}")
        purrr::walk(add_cols, ~DBI::dbExecute(con, .x))
        purrr::walk(update_cols, ~DBI::dbExecute(con, .x))
        purrr::walk(drop_cols, ~DBI::dbExecute(con, .x))
        purrr::walk(rename_cols, ~DBI::dbExecute(con, .x))
    }

I imagine there may be a better to do this, but it has worked for me so far. To be fair, I don't need to do a ton of writing from R to an Oracle DB, so it hasn't been thoroughly tested.

The issue with odbc and dates may be fixable, but I haven't gotten there yet. See here where odbc converts date columns to VARCHAR(255) by default for Oracle:

#9

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