function tbl doesn't work anymore

[DrFabach]

I use dbplyr to extract information from an oracle database with odbc driver.
I download last version of dbplyr and dyplr and since yesterday i got some errors with tbl().

My connection is active in rstudio and i am able to see the table i want to work with .
I am able to use sql to get data from table.

When i use this fonction

tbl(con, in_schema("schema","table"))

I got the following error :

Erreur : nanodbc/nanodbc.cpp:1655: 42S02: [Oracle][ODBC][Ora]ORA-00942: table or view does not exist

'SELECT *
FROM ("schema"."table") "q01"
WHERE (0 = 1)'

[einarhjorleifsson]

I am aware that with dbplyr version 2 schema and table name calls have become case sensitive.
so if table name is e.g. all-caps this no longer works:

tbl(con, in_schema("schema", "table"))

but this does:

tbl(con, in_schema("SCHEMA", "TABLE"))

may this be bugging you?
e

[DrFabach]

Thank you for your answer
I already tried it and this give me this error :

Erreur : nanodbc/nanodbc.cpp:1655: HY000: [Oracle][ODBC][Ora]ORA-00933: SQL command not properly ended

'SELECT *
FROM ("SCHEMA"."REPONSE_TEXTE_LONG")
FETCH FIRST 11 ROWS ONLY'

I cannot collect() data.

[DrFabach]

The error come frome the "FETCH FIRST 11 ROWS ONLY"

[DrFabach]

issue is reported here : #27

I find a dirty way to correct it.

I changed the function dbplyr:::sql_query_select.Oracle
from :
function (con, select, from, where = NULL, group_by = NULL, having = NULL, order_by = NULL, limit = NULL, distinct = FALSE, ..., subquery = FALSE) { sql_select_clauses(con, select = sql_clause_select(con, select, distinct), from = sql_clause_from(con, from), where = sql_clause_where(con, where), group_by = sql_clause_group_by(con, group_by), having = sql_clause_having(con, having), order_by = sql_clause_order_by(con, order_by, subquery, limit), limit = if (!is.null(limit)) { build_sql("FETCH FIRST ", as.integer(limit), " ROWS ONLY", con = con) }) }

to

function (con, select, from, where = NULL, group_by = NULL, having = NULL, order_by = NULL, limit = NULL, distinct = FALSE, ..., subquery = FALSE) { sql_select_clauses(con, select = sql_clause_select(con, select, distinct), from = sql_clause_from(con, from), where = sql_clause_where(con, where), group_by = sql_clause_group_by(con, group_by), having = sql_clause_having(con, having), order_by = sql_clause_order_by(con, order_by, subquery, limit), limit = if (!is.null(limit)) { if (!length(where) == 0) { build_sql("AND ROWNUM <= ", as.integer(limit), con = con) } else { build_sql("WHERE ROWNUM <= ", as.integer(limit), con = con) } }) }

[fschiefenhoevel]

Hi!

is there a way you could help me to implement your solution in the current dbplyr version? I am stuck with the same problem (oracle 11 not being supported, FETCH FIRST not working), and I can't fix it with your code (since dbplyr has changed too much, I guess)

[mgirlich]

This is fixed in the dev version which should be released to CRAN soon.

[fschiefenhoevel]

ahh, that would be supergood. Thanks!