copy_inline() not working on oracle backends

[bthe]

This is basically a repeat of the issues listed in #950, but for Oracle backends.

copy_inline(con, tibble(a=c(1,1), b=c(2,3)))

Error in .oci.SendQuery(conn, statement, data = data, prefetch = prefetch, :
ORA-00903: invalid table name

As for the Hana DB it seems the values command does not yield the desired result and you need use union all in combination with selecting from dual in the query. So instead of the previous query this works:

SELECT CAST("a" AS NUMBER) AS "a", CAST("b" AS NUMBER) AS "b"
FROM (
  (
    SELECT NULL AS "a", NULL AS "b"
    FROM dual 
    WHERE (0 = 1)
  )
  UNION ALL
  (select 1.0, 2.0 from dual union all select 1.0, 3.0 from dual)
) "values_table"

[mgirlich]

@bthe Is the FROM dual required? Would be great if you could test out my implementation (install via install_github("tidyverse/dbplyr#982")) and give feedback.

[bthe]

It seems that the FROM DUAL is required, at least:

SELECT NULL AS "a", NULL AS "b"
    FROM () 
    WHERE (0 = 1)

fails whereas

SELECT NULL AS "a", NULL AS "b"
    FROM DUAL
    WHERE (0 = 1)

yields the expected results.

[mgirlich]

Thanks, indeed the FROM DUAL is necessary. I updated the PR and it should work now. Would be great if you could confirm 😄

[bthe]

Almost there, the generated query still has () in the definition clause (line 5 below):

SELECT CAST("a" AS NUMBER) AS "a", CAST("b" AS NUMBER) AS "b"
FROM (
  (
    SELECT NULL AS "a", NULL AS "b"
    FROM () 
    WHERE (0 = 1)
  )
  UNION ALL
  (
    SELECT 1.0, 2.0 FROM DUAL UNION ALL 
    SELECT 1.0, 3.0 FROM DUAL
  )
) "values_table"

[mgirlich]

Ah, thanks. This is now fixed as well.

[bthe]

Super thanks, this now works as it should:)