CODEWITHSUNDEEP
roracle-databasejdbc
Davit
Davit

Reputation: 107

How to upload data into Oracle db from R?

I need to upload data from R into Oracle database. I follow to the recommendations from this question how to export data frame (R) into Oracle table.

I use the code:

dbWriteTable(con_dwh, "table_db", table_in_R, 
         append =T, row.names=F, overwrite = F)

where con_dwh is a JDBC conection to Oracle database.

Hovewer I've got the error message:

Error in .local(conn, statement, ...) : 
execute JDBC update query failed in dbSendUpdate (NaN)

I am a little bit confused on the error occured.

Could someone help me?

Upvotes: 1

Views: 1140

Answers (1)

Marmite Bomber
Marmite Bomber

Reputation: 21095

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.

Upvotes: 1

Related Questions