Missing `ON COMMIT PRESERVE ROWS` in Oracle `CREATE GLOBAL TEMPORARY TABLE`

tidyverse / dbplyr

Database (DBI) backend for dplyr

https://dbplyr.tidyverse.org

Other

473 stars 171 forks source link

Missing `ON COMMIT PRESERVE ROWS` in Oracle `CREATE GLOBAL TEMPORARY TABLE` #750

Open admivsn opened 2 years ago

admivsn commented 2 years ago

I believe the bug is in https://github.com/tidyverse/dbplyr/blob/main/R/backend-oracle.R line 120:

Should look more like this https://github.com/r-dbi/odbc/blob/74ad62c275b1c95a70814707901bdafd8fc2d2b6/R/db.R#L32 line 15:

The effect of this is that anything with temporary = TRUE (e.g. copy_to or compute will result in an empty temporary table. Using {odbc} to connect.

rgriffier commented 1 year ago

Hi! I just post a new comment on an old issue about the same subject. Withe the PRIVATE TEMPORARY TABLE (available since Oracle 18c) the data structure will be droped at the end of the session (documentation here). I think this is the right way to create temporary tables in Oracle in the same way as in other DBMS.

hadley commented 9 months ago

Looks like it will need a standard prefix, ora$ptt_, like SQL server.