backend-oracle.md
In dbplyr: A 'dplyr' Back End for Databases

queries translate correctly

Code
  mf %>% head()
Output
  <SQL>
  SELECT `df`.*
  FROM `df`
  WHERE (ROWNUM <= 6)

`sql_query_upsert()` is correct

Code
  sql_query_upsert(con = con, table = ident("df_x"), from = sql_render(df_y, con,
    lvl = 1), by = c("a", "b"), update_cols = c("c", "d"), returning_cols = c("a",
    b2 = "b"), method = "merge")
Output
  <SQL> MERGE INTO `df_x`
  USING (
    SELECT `a`, `b`, `c` + 1.0 AS `c`, `d`
    FROM `df_y`
  ) `...y`
    ON (`...y`.`a` = `df_x`.`a` AND `...y`.`b` = `df_x`.`b`)
  WHEN MATCHED THEN
    UPDATE SET `c` = `...y`.`c`, `d` = `...y`.`d`
  WHEN NOT MATCHED THEN
    INSERT (`a`, `b`, `c`, `d`)
    VALUES (`...y`.`a`, `...y`.`b`, `...y`.`c`, `...y`.`d`)
  RETURNING `df_x`.`a`, `df_x`.`b` AS `b2`
  ;

generates custom sql

Code
  sql_table_analyze(con, in_schema("schema", "tbl"))
Output
  <SQL> ANALYZE TABLE `schema`.`tbl` COMPUTE STATISTICS

Code
  sql_query_explain(con, sql("SELECT * FROM foo"))
Output
  <SQL> EXPLAIN PLAN FOR SELECT * FROM foo;
  SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY()));

Code
  left_join(lf, lf, by = "x", na_matches = "na")
Output
  <SQL>
  SELECT `df_LHS`.`x` AS `x`
  FROM `df` `df_LHS`
  LEFT JOIN `df` `df_RHS`
    ON (decode(`df_LHS`.`x`, `df_RHS`.`x`, 0, 1) = 0)

Code
  sql_query_save(con, sql("SELECT * FROM foo"), in_schema("schema", "tbl"))
Output
  <SQL> CREATE GLOBAL TEMPORARY TABLE `schema`.`tbl` AS
  SELECT * FROM foo

Code
  sql_query_save(con, sql("SELECT * FROM foo"), in_schema("schema", "tbl"),
  temporary = FALSE)
Output
  <SQL> CREATE TABLE `schema`.`tbl` AS
  SELECT * FROM foo

Code
  slice_sample(lf, n = 1)
Output
  <SQL>
  SELECT `x`
  FROM (
    SELECT `df`.*, ROW_NUMBER() OVER (ORDER BY DBMS_RANDOM.VALUE()) AS `col01`
    FROM `df`
  ) `q01`
  WHERE (`col01` <= 1)

copy_inline uses UNION ALL

Code
  copy_inline(con, y %>% slice(0)) %>% remote_query()
Output
  <SQL> SELECT CAST(NULL AS INT) AS `id`, CAST(NULL AS VARCHAR2(255)) AS `arr`
  FROM `DUAL`
  WHERE (0 = 1)
Code
  copy_inline(con, y) %>% remote_query()
Output
  <SQL> SELECT CAST(`id` AS INT) AS `id`, CAST(`arr` AS VARCHAR2(255)) AS `arr`
  FROM (
    SELECT NULL AS `id`, NULL AS `arr`
    FROM `DUAL`
    WHERE (0 = 1)

    UNION ALL

    SELECT 1, '{1,2,3}' FROM DUAL
  ) `values_table`
Code
  copy_inline(con, y %>% slice(0), types = types) %>% remote_query()
Output
  <SQL> SELECT CAST(NULL AS bigint) AS `id`, CAST(NULL AS integer[]) AS `arr`
  FROM `DUAL`
  WHERE (0 = 1)
Code
  copy_inline(con, y, types = types) %>% remote_query()
Output
  <SQL> SELECT CAST(`id` AS bigint) AS `id`, CAST(`arr` AS integer[]) AS `arr`
  FROM (
    SELECT NULL AS `id`, NULL AS `arr`
    FROM `DUAL`
    WHERE (0 = 1)

    UNION ALL

    SELECT 1, '{1,2,3}' FROM DUAL
  ) `values_table`