Uploading Data with NA to Oracle


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.


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

If you have a query related to it or one of the replies, start a new topic and refer back with a link.