dbplyr Oracle alias limit database

Question

After a lengthy troubleshooting process I finally got RStudio to connect to my works Oracle database using the instant client. The motivation to do this was to use dbplyr to do most of my querying. However, I am unable to send any queries through dbplyr as all the column and table names are very long and result in ORA-00972: identifier is too long.

Example Error

 test <- tbl(con, "long_shcema_name.very_long_and_descriptive_name") %>%
   select(a_column)

Error: nanodbc/nanodbc.cpp:1617: 42000: [Oracle][ODBC][Ora]ORA-00972: identifier is too long

<SQL> 'SELECT *
FROM ("long_shcema_name.very_long_and_descriptive_name") "zzz7"
WHERE (0 = 1)'

I thought the issue was driver related. My work uses Oracle 12.2 which has a larger cap for identifiers, but dbGetQuery works fine over instantclient

Working example without dbplyr

dbGetQuery(con,'
  select "a_column"
  from 
  long_shcema_name.very_long_and_descriptive_name')

a_column
    1
    0
    2
    .
    .
    .

This gives me the desired results.

Does a solution exist?

I'm a little irked. It took me almost three weeks to get the odbc connection set up with my works database using the instant client. I was able to query using RODBC and ROracle from the get go, but wanted to use dplyr. It ended up not working and I don't have a whole lot of juice left to figure out why. Any help would be immensely appreciated at this point.

Do I understand you correctly that the second solution works for you but you cannot use dbplyr for this? Are you able to retreive the SQL-code by piping `get_query()` at the end? — mnist, Jan 11 '20 at 00:31
I’ve run into this before and sometimes (not always) using the [in_schema](https://dbplyr.tidyverse.org/reference/in_schema.html) function works. I think for you it would be something like `tbl(con, in_schema("long_shcema_name”, “very_long_and_descriptive_name"))` — Wil, Jan 11 '20 at 02:41
The in_schema function is working. Do you have any idea of when it doesn't work? I usually try to specify the schema in the connection string so I am wondering if some more compatibility between odbc and dbplyr could be introduced. — aodwbag, Jan 13 '20 at 16:29

score 1 · Accepted Answer · answered Jan 11 '20 at 04:35

The issue is most likely not long file names (unless your table names are longer than 30 characters, see here) but the use of a schema. Oracle can use double quotes to identify database, schema and table names (ref). Square brackets are sometimes used for this purpose too (ref).

Take a look at the SQL that produces the error message:

SELECT *
FROM ("long_schema_name.very_long_and_descriptive_name") "zzz7"
WHERE (0 = 1)

This is most likely being interpreted as:

database = {default}
schema = {empty}
table = long_schema_name.very_long_and_descriptive_name

Instead of as:

database = {default}
schema = long_schema_name
table = very_long_and_descriptive_name

As @Wil comments you can address this with the in_schema function. If you try:

 test <- tbl(con, in_schema("long_schema_name","very_long_and_descriptive_name") %>%
   select(a_column)

Then show_query(test) should return:

SELECT *
FROM ("long_schema_name"."very_long_and_descriptive_name") "zzz7"
WHERE (0 = 1)

Note how this differs from the first query by the addition of double quotes either side of the full stop.

dbplyr Oracle alias limit database

Example Error

Working example without dbplyr

Does a solution exist?

1 Answers1