Oracle: copy_to with overwrite fails

[EdwardJRoss]

I am trying to send a table to an Oracle Database via ODBC ,overwriting if it already exists.
The copy_to method in Oracle via ODBC fails when overwrite=TRUE because "DROP TABLE IF EXISTS" isn't valid syntax in Oracle.

Example (without specific credentials), tested with Oracle 11.2:

con <- DBI::dbConnect(odbc::odbc(), ...)
dplyr::copy_to(con, iris, overwrite=TRUE)
#> Error: <SQL> 'DROP TABLE IF EXISTS iris'
#> nanodbc/nanodbc.cpp:1587: HY000: [Oracle][ODBC][Ora]ORA-00933: SQL command not properly ended
dplyr::copy_to(con, iris, overwrite=TRUE)
#> Error in connection_begin(conn@ptr) : Double begin

Expected result:
The table iris is loaded to the database, being overwritten if it already exists.

Actual Result:
There is an error, and the connection can no longer be used (it seems the transaction isn't aborted on failure).

I also tried using ROracle but copy_to seems to fail when attempting to start a transaction:

con <- DBI::dbConnect(DBI::dbDriver("Oracle"), ...)
dplyr::copy_to(con, iris)
#> Error in (function (classes, fdef, mtable)  : 
#>  unable to find an inherited method for function 'dbBegin' for signature '"OraConnection"'

[krlmlr]

Thanks for reporting this. Is there an equivalent to DROP TABLE IF EXISTS in Oracle?

[EdwardJRoss]

Unfortunately only a messy one, try to drop the table and catch the "table not exists error" if it is thrown.

BEGIN
   EXECUTE IMMEDIATE 'DROP TABLE <the table>';
EXCEPTION
   WHEN OTHERS THEN
      IF SQLCODE != -942 THEN
         RAISE;
      END IF;
END;

[krlmlr]

Thanks. This could be good enough for dbplyr, if we can't do any better.

[hadley]

@edgararuiz do you want to take this one?

[edgararuiz-zz]

Yes, I'll take this one

[ghost]

This issue was moved by krlmlr to tidyverse/dbplyr/issues/120.

[lock]

This old issue has been automatically locked. If you believe you have found a related problem, please file a new issue (with reprex) and link to this issue. https://reprex.tidyverse.org/