Oracle backend not treated correctly

[DSLituiev]

trafficstars

The oracle backend does not get recognised (v. 2.5.0 and main branch @HEAD)

When I force backend to Oracle it works, but I am not sure how to force it in tbl statement:

drv <- JDBC("oracle.jdbc.driver.OracleDriver", "~/lib/instantclient_19_8/ojdbc8.jar")
conn <- dbConnect(drv, 
                  "jdbc:oracle:thin:@host:port/...",
                  user=credentials$user,
                  password=credentials$pwd
)

conn
# <JDBCConnection>
attr(conn, "jc")
# [1] "Java-Object{oracle.jdbc.driver.T4CConnection@...}"

dim_xyz <- tbl(conn, in_schema("MYSCHEMA", "DIM_XYZ"))

dim_xyz

Trying to print the table gets an error:

Error in `collect()`:
! Failed to collect lazy table.
Caused by error in `dbSendQuery()`:
! Unable to retrieve JDBC result set
  JDBC ERROR: ORA-00933: SQL command not properly ended

  Statement: SELECT "DIM_XYZ".*
FROM "MYSCHEMA"."DIM_XYZ"
LIMIT 11

Here are some attempts to get to why it does not use the proper backend:

dbplyr:::test_translate_sql(head, con=conn)
# <SQL> "head"

> dbplyr:::sql_query_select(conn, sql("*"), ident("x"), where=ident("y"), limit=3)
<SQL> SELECT *
FROM "x"
WHERE (y)
LIMIT 3


> dbplyr:::sql_query_select.Oracle(conn, sql("*"), ident("x"), where=ident("y"), limit=3)
<SQL> SELECT *
FROM "x"
WHERE (y)
FETCH FIRST 3 ROWS ONLY

mf <- lazy_frame(x = 1, con = simulate_oracle())
> mf %>% head()
<SQL>
SELECT `df`.*
FROM `df`
FETCH FIRST 6 ROWS ONLY

[DSLituiev]

workaround: wrap the connection into a new S4 class with jc slot copied from the original connection object:

setClass("Oracle", slots=list(),
         contains = "JDBCConnection")
conn_fixed <- new("Oracle", jc = slot(conn, "jc"))

[ablack3]

Here is a reprex that seems to work correctly. We also had this issue on a live Oracle v19 database though.

library(dplyr)
#> 
#> Attaching package: 'dplyr'
#> The following objects are masked from 'package:stats':
#> 
#>     filter, lag
#> The following objects are masked from 'package:base':
#> 
#>     intersect, setdiff, setequal, union
library(dbplyr)
#> 
#> Attaching package: 'dbplyr'
#> The following objects are masked from 'package:dplyr':
#> 
#>     ident, sql

con <- dbplyr::simulate_oracle()

person <- dbplyr::lazy_frame(
  observation_period_start_date = date(),
  year_of_birth = integer(),
  .name = "my_table",
  con = con
)
person %>%
  mutate(age = year(observation_period_start_date) - year_of_birth) %>%
  distinct(age) %>%
  rename(person_age = age) %>%
  arrange(desc(person_age)) %>%
  head(10) 
#> <SQL>
#> SELECT DISTINCT EXTRACT(year FROM `observation_period_start_date`) - `year_of_birth` AS `person_age`
#> FROM `my_table`
#> ORDER BY `person_age` DESC
#> FETCH FIRST 10 ROWS ONLY

^{Created on 2025-02-14 with reprex v2.1.1}

Session info

sessioninfo::session_info()
#> ─ Session info ───────────────────────────────────────────────────────────────
#>  setting  value
#>  version  R version 4.3.3 (2024-02-29)
#>  os       macOS Sonoma 14.1
#>  system   aarch64, darwin20
#>  ui       X11
#>  language (EN)
#>  collate  en_US.UTF-8
#>  ctype    en_US.UTF-8
#>  tz       UTC
#>  date     2025-02-14
#>  pandoc   3.1.11 @ /Applications/RStudio.app/Contents/Resources/app/quarto/bin/tools/aarch64/ (via rmarkdown)
#> 
#> ─ Packages ───────────────────────────────────────────────────────────────────
#>  package     * version date (UTC) lib source
#>  blob          1.2.4   2023-03-17 [1] CRAN (R 4.3.0)
#>  cli           3.6.3   2024-06-21 [1] CRAN (R 4.3.3)
#>  DBI           1.2.3   2024-06-02 [1] CRAN (R 4.3.3)
#>  dbplyr      * 2.5.0   2024-03-19 [1] CRAN (R 4.3.1)
#>  digest        0.6.37  2024-08-19 [1] CRAN (R 4.3.3)
#>  dplyr       * 1.1.4   2023-11-17 [1] CRAN (R 4.3.1)
#>  evaluate      1.0.1   2024-10-10 [1] CRAN (R 4.3.3)
#>  fastmap       1.2.0   2024-05-15 [1] CRAN (R 4.3.3)
#>  fs            1.6.5   2024-10-30 [1] CRAN (R 4.3.3)
#>  generics      0.1.3   2022-07-05 [1] CRAN (R 4.3.0)
#>  glue          1.8.0   2024-09-30 [1] CRAN (R 4.3.3)
#>  htmltools     0.5.8.1 2024-04-04 [1] CRAN (R 4.3.1)
#>  knitr         1.49    2024-11-08 [1] CRAN (R 4.3.3)
#>  lifecycle     1.0.4   2023-11-07 [1] CRAN (R 4.3.1)
#>  magrittr      2.0.3   2022-03-30 [1] CRAN (R 4.3.0)
#>  pillar        1.10.1  2025-01-07 [1] CRAN (R 4.3.3)
#>  pkgconfig     2.0.3   2019-09-22 [1] CRAN (R 4.3.0)
#>  purrr         1.0.2   2023-08-10 [1] CRAN (R 4.3.0)
#>  R6            2.5.1   2021-08-19 [1] CRAN (R 4.3.0)
#>  reprex        2.1.1   2024-07-06 [1] CRAN (R 4.3.3)
#>  rlang         1.1.5   2025-01-17 [1] CRAN (R 4.3.3)
#>  rmarkdown     2.29    2024-11-04 [1] CRAN (R 4.3.3)
#>  rstudioapi    0.17.1  2024-10-22 [1] CRAN (R 4.3.3)
#>  sessioninfo   1.2.2   2021-12-06 [1] CRAN (R 4.3.0)
#>  tibble        3.2.1   2023-03-20 [1] CRAN (R 4.3.0)
#>  tidyselect    1.2.1   2024-03-11 [1] CRAN (R 4.3.1)
#>  vctrs         0.6.5   2023-12-01 [1] CRAN (R 4.3.1)
#>  withr         3.0.2   2024-10-28 [1] CRAN (R 4.3.3)
#>  xfun          0.49    2024-10-31 [1] CRAN (R 4.3.3)
#>  yaml          2.3.10  2024-07-26 [1] CRAN (R 4.3.3)
#> 
#>  [1] /Library/Frameworks/R.framework/Versions/4.3-arm64/Resources/library
#> 
#> ──────────────────────────────────────────────────────────────────────────────

[lentinj]

This is essentially a duplicate of https://github.com/tidyverse/dbplyr/issues/1359, while the symptoms described are different the root cause is the same AFAICS.

Your workaround is an interesting approach not discussed on the other issue though.