search

s-u / RJDBC

R interface to databases using JDBC
Other
52 stars 47 forks source link

Cannot use either dbWriteTable or dbAppendTable with Oracle databases #100

Open connorH982 opened 1 year ago

connorH982 commented 1 year ago

Splitting off from a older separate issue, since it seems specific to Oracle and new problem: https://github.com/s-u/RJDBC/issues/55

Oracle Version: Oracle Database 19c Enterprise Edition 19.0.0.0.0 -- Version 19.17.0.0.0

It seems RJDBC doesn't seem to work with appending data to existing tables on Oracle?

The below works:

    con <- RJDBC::dbConnect(RSQLite::SQLite(), ":memory:")
    sample_data <-data.table(letters = letters,numbers = seq(letters))
    dbRemoveTable(con,"JDBC_test_table")
    dbWriteTable(con,"JDBC_test_table",sample_data,append = TRUE,overwrite = FALSE) # Create a table: Success
    dbWriteTable(con,"JDBC_test_table",sample_data,append = TRUE,overwrite = FALSE) # Append: Success on SQLite
    check <- dbGetQuery(con,"SELECT * FROM JDBC_test_table;")
    dbDisconnect(con)

This does not:


    con <- anyOracleConnection()
    sample_data <-data.table(letters = letters,numbers = seq(letters))
    dbRemoveTable(con,"JDBC_test_table")
    dbWriteTable(con,"JDBC_test_table",sample_data,append = TRUE,overwrite = FALSE) # Create a table: Success
    dbWriteTable(con,"JDBC_test_table",sample_data,append = TRUE,overwrite = FALSE) # Append: Error on  Oracle
    check <- dbGetQuery(con,"SELECT * FROM JDBC_test_table;")
    dbDisconnect(con)

Edit: To add to this, I was also noticing this very contradictory behavior with Oracle tables. I think it is related to not correctly detecting if a table exists or not?

>     RJDBC::dbExistsTable(con,"JDBC_test_table")
[1] TRUE
>     RJDBC::dbRemoveTable(con,"JDBC_test_table")
Error in .local(conn, statement, ...) : 
  execute JDBC update query failed in dbSendUpdate
  JDBC ERROR: ORA-00942: table or view does not exist

  Statement: DROP TABLE JDBC_test_table
>  RJDBC::dbWriteTable(con,"JDBC_test_table",sample_data)
Error in .local(conn, name, value, ...) : 
  Table `JDBC_test_table' already exists