How to upload data into Oracle db from R?

For some reasons in dbWriteTable a NA value in a num columns fails to be inserted as NULL, which works fine for chr column.

So the workaround is to replace the NA in the num column with a valid number - i#m using `0``.

If you need to end with nullin the table use some special number and UPDATE to null in the database.

> df <- data.frame(col1 = c('x',NA,'y'), col2 = c(1,NA,NA))
> 
> df
  col1 col2
1    x    1
2 <NA>   NA
3    y   NA
> str(df)
'data.frame':   3 obs. of  2 variables:
 $ col1: chr  "x" NA "y"
 $ col2: num  1 NA NA
> 
> dbWriteTable(jdbcConnection,"TEST", df,   rownames=FALSE, overwrite = TRUE, append = FALSE)  
Fehler in .local(conn, statement, ...) : 
  execute JDBC update query failed in dbSendUpdate (NaN)
> 
> df[is.na(df$col2),"col2"] <- 0
> df
  col1 col2
1    x    1
2 <NA>    0
3    y    0
> 
> dbWriteTable(jdbcConnection,"TEST", df,   rownames=FALSE, overwrite = TRUE, append = FALSE) 
> 

In the DB

select * from test

COL1           COL2
-------- ----------
x                 1
                  0
y                 0

For integer data type int the insert of NA works but the inserted value is -2147483648 which is also not good.

df <- data.frame(  id  = c(as.integer(NA),as.integer(NA)) )
dbWriteTable(jdbcConnection,"TEST", df,   rownames=FALSE, overwrite = TRUE, append = FALSE) 

## OK but

select * from test

ID
-2147483648
-2147483648

This has probably to do with the internal storage of NA as integer.

Note produced with R version 4.0.4 and RJDBC_0.2-8.