Hello,
I am working on uploading data to an Oracle server using a JDBC connection and using the function "dbWriteTable" from the DBI package. I normally don't have a problem uploading data using this method, though when I try to load any tables with NA values , I get an error saying that NaN values are not permitted. I researched this issue and this should only happen for me if I have NaN or Inf values, though I only have values that are NA in my table, and I have tested this with sample tables as well.
Another interesting component of this issue is that if I have a table that has missing values, I can upload the first row of this table using dbWriteTable(), though when I try to upload two or more rows of the table, I get the error.
The error I get is as follows:
Error in .local(conn, statement, ...) :
execute JDBC update query failed in dbSendUpdate (NaN)
An example of the code I generated to test this issue is as follows:
test_matrix <- data.frame(matrix(c(1,2,3,4,5,NA),nrow=2))
DBI::dbWriteTable(conn, "test", test_matrix, rownames=FALSE, overwrite=FALSE, append=FALSE)
As you can see, this code generates a 2 X 3 matrix with one NA value. If I were to select the bottom row and upload that single row, the upload works perfectly fine. As soon as I try to upload the whole table, I get the error.
I am using R-4.1.0 and an up to date R studio as of 10/15/2021
Any help with this issue would be greatly appreciated. I feel completely stuck and am not sure how to fix the issue and I do not know of any other ways to upload data to Oracle.
Thanks!