Incorrect SQL generated against Oracle databases when no schema is specified

[mungojam]

When using tbl() with our Oracle database, we have to provide the schema name using in_schema. But when calling dbGetQuery directly we seem to get away without it.

Looks like this would be fixed by removing the brackets and speech marks from the table name if there is no schema:

#Setup connection
con <- dbConnect(
                odbc::odbc(),
                driver = "Oracle in INSTANTCLIENT_12_1",
                Dbq = "TheDatabase"
            )

#This works
con %>% dbGetQuery("select * from my_table")

#This doesn't
con %>% tbl("my_table")

#Error: <SQL> 'SELECT *
#FROM ("my_table") "zzz16"
#WHERE (0 = 1)'
#nanodbc/nanodbc.cpp:1587: 42S02: [Oracle][ODBC][Ora]ORA-00942: table or view does not exist

#Needs the schema to be specified to make it work
con %>% tbl(in_schema("SCHEMA_OWNER", "my_table"))

#Manually running the query without brackets or speech marks works:
con %>% dbGetQuery("SELECT * FROM my_table \"zzz16\" WHERE (0 = 1)")

#With speech marks doesn't (as per the error message)
con %>% dbGetQuery("SELECT * FROM (\"my_table\") \"zzz16\" WHERE (0 = 1)")

[edgararuiz-zz]

Hi @mungojam , is the SCHEMA_OWNER in this case you?

[mungojam]

Good question. No, it's where most of our tables live and we all have access to tables within it (don't know if within is the right term, schemas confuse me..).

Don't know if you saw my edit, it looks like just removing the surrounding brackets and speech-marks in the generated SQL would fix it. I guess that means that there is a default schema thing going on under the covers. I would do a pull request, but I'm a bit snowed under at the moment.

[rlusch]

I have encountered this same issue, though I hadn't tried specifying a schema as a work around yet. It seems to work well at this point, though it would be nice if it worked in the specified manner.

[hadley]

This is going to need some digging by someone who knows more about Oracle's SQL dialect than me. Simply removing the quotes is not an option because that will cause a different set of problems with reserved words, case sensitivity, etc.

[mungojam]

One thing I didn't check was whether just removing the brackets and leaving the speech marks would have done the trick. I'm not at work for a week now to check.

@rlusch would you mind checking to see if the following query works?

con %>% dbGetQuery("SELECT * FROM \"my_table\" \"zzz16\" WHERE (0 = 1)")

[hadley]

A good reference for oracle quoting rules appears to be this: https://docs.oracle.com/cd/B28359_01/server.111/b28286/sql_elements008.htm#SQLRF00223 (although I think it's using schema in a different sense to dbplyr)

I think the most likely explanation is that the name of your table is actually "MY_TABLE". Since dbplyr uses quoted identifiers everywhere, you need to refer to tables by their actual names, which are typically in upper case.

[mungojam]

That is a useful page of explanation, so thank you, and you are right about the capitalisation, it does work when I use all caps. It's Oracle design at fault in my view. However, I am the first person in our area to really use R with Oracle and I fully expect others are going to get hit by this once we start moving to R more. It's going to be a pain point.

I know it's a long shot to ask this, but it would be ideal for us if the default was for it to automatically toUpper the table name before quoting unless the user passed in the table name in back-ticks or something, e.g. `my_Table`. Is it worth me raising an issue for this request, or will it just get rejected?

People are so used to not worrying about case when just writing a query in toad or whatever tool and I can't imagine many companies having tables with only the capitalisation varying.

[hadley]

Yeah, unfortunately while that would solve your immediate problem, my sense is that it will create more problems in the longer run.

[mungojam]

ok, understood. I'll drill in to people to use ALL_CAPs whenever they can. They'll need to get used to it for field names anyway

[mungojam]

Incidentally, when using the schema (which allows for lower caps), the names don't look like they are quoted, at least no speech marks, but maybe that's what tidyverse/dbplyr@3cc019d is fixing (I can't try out GitHub releases at work):

tbl(con, in_schema("schema_owner", "my_table")) %>%
filter(X >= 13) %>%
     show_query()

Leads to unquoted and therefore working query:

SELECT *
FROM (schema_owner.my_table) 
WHERE ("X" >= 13.0)