Oracle explain() SQL command not properly ended

[thomashulst]

When trying to explain a query on an Oracle backend, the function errors as follows:

library(DBI) 
library(dplyr) 
library(dbplyr)
library(ROracle) 

con <- dbConnect(dbDriver("Oracle"), "PDB",
                   username = "USER"
                   password = "PWD")

tbl(con, in_schema("SCHEMA","TEST")) |> explain()
#> <SQL>
#> SELECT *
#> FROM "SCHEMA"."TEST"
#> 
#> <PLAN>
#> Error in `db_explain.DBIConnection()`:
#> ! Can't explain query.
#> Caused by error in `.oci.GetQuery()`:
#> ! ORA-00933: SQL command not properly ended
#> Backtrace:
#>      x
#>   1. +-dplyr::explain(tbl(con, in_schema("SCHEMA", "TEST")))
#>   2. \-dbplyr:::explain.tbl_sql(tbl(con, in_schema("SCHEMA", "TEST")))
#>   3.   +-dbplyr:::cat_line(remote_query_plan(x, ...))
#>   4.   | +-base::cat(paste0(..., "\n"), sep = "")
#>   5.   | \-base::paste0(..., "\n")
#>   6.   \-dbplyr::remote_query_plan(x, ...)
#>   7.     \-dbplyr:::dbplyr_explain(...)
#>   8.       \-dbplyr:::dbplyr_fallback(con, "db_explain", ...)
#>   9.         +-rlang::eval_bare(expr((!!fun)(con, ...)))
#>  10.         \-dbplyr:::db_explain.DBIConnection(con, ...)
#>  11.           \-base::tryCatch(...)
#>  12.             \-base (local) tryCatchList(expr, classes, parentenv, handlers)
#>  13.               \-base (local) tryCatchOne(expr, names, parentenv, handlers[[1L]])
#>  14.                 \-value[[3L]](cond)
#>  15.                   \-cli::cli_abort("Can't explain query.", parent = cnd)
#>  16.                     \-rlang::abort(...)

^{Created on 2023-08-11 with reprex v2.0.2}

I identified two issues with sql_query_explain.Oracle:

1. There seems to be a superfluous parentheses in DBMS_XPLAN.DISPLAY())
2. I'm not exactly sure what is happening, but it seems EXPLAIN PLAN FOR returns something which messes up further execution? It appears dbExecute can be used for the EXPLAIN PLAN FOR part, as exemplified by this old PR Adds support for explain() for Oracle connections #80

I will see if I can submit a PR.