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.
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.
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.
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: