0

I am having issues with pushing data with NA values in Oracle using the dbWriteTable(), which comes from R's DBI package. When I try to upload this data frame to the desired oracle database, I get an error message saying:

Error in .local(conn, statement, ...) :

execute JDBC update query failed in dbSendUpdate (NaN)

I noticed that when I upload the first row of this data frame, I have no problems, though if I try to upload two or more rows in the data frame, I get this error message. There are no NaN or Inf values in this data frame. There are only character and numeric variables and certain numeric variables contain NA values.

X1 X2
NA 5
2 6
3 7
4 8

The table above is an example of a table that will return the error message. If I were to upload only the first row of this table, everything works properly, and the first row is outputted into the Oracle database.

My thought is that there is a bug in the dbWriteTable() function that I cannot get around by continuing to use it. Does anyone know of a way that I can fix this issue, or if there are any other methods that I use to complete my task?

Code:

dbWriteTbale(conn = jdbcConnection,name = "test", value = test, row.names=FALSE, append=FALSE, overwrite=FALSE)

My session info is below.

R version 4.1.0 (2021-05-18)

Platform: x86_64-w64-mingw32/x64 (64-bit)

Running under: Windows Server 2012 R2 x64 (build 9600)

Vinícius Félix
  • 8,448
  • 6
  • 16
  • 32
Tony Acosta
  • 1
  • Usually NA is replaced with NULL automatically. I don't know why that isn't working here. Can you replace the NA's with a number that won't otherwise be in the table, like -1? Then after dbWriteTable you could replace them with null using SQL. – Brian Montgomery Sep 30 '21 at 02:44
  • Are you running division somewhere in your calculations? How did you check for `Inf` and `NaN`? Don't feely on visual, human inspection. Try converting with: `df[is.na(df)] <- NA`. Also, check if the destination column in table allows `NULL` values. – Parfait Oct 02 '21 at 19:31
  • There are no Inf or NaN values in this data frame. I replicated the error with the sample data frame in this post, and it only has 1 value that I manually created as an NA value. I am just confused why this issue could be happening, because from what I understand, I should not be having this problem. I can upload the first row and the first row of data looks perfect in the Oracle data base, though when I try to upload two or more rows, I get this error. – Tony Acosta Oct 15 '21 at 17:22

0 Answers0