Oracle Analytics Cloud (OAC): invalid identifier error with mixed case column names in Snowflake
Set Double Quotes as Quoting Identifiers on the Advanced tab of Connection
Problem: ‘invalid identifier’ error when connecting to columns which are named using mixed case in Snowflake database.
Solution: in the Advanced tab of the connection, turn on Quoting Identifiers: Double Quotes (“ “).
The situation
Working on a project to connect to a Snowflake database in Oracle Analytics Cloud (OAC), I ran into an ‘invalid identifier’ error. I had set it up in a similar way to previous connections which worked. First, you add connections to specific tables. Next, you create a data model that brings in those columns. It’s at the data model level that you have a definition for each table, which includes a SQL statement.
Troubleshooting
Originally, I used a SELECT * FROM <server.schema.table> statement. When I hit the ‘invalid error’, I changed the statement to SELECT “OneMixedCaseColumn” FROM <server.schema.table>. Selecting one column only enabled me to reproduce the error in a narrow way.
Here’s the relevant excerpt of the error:
State: HY000. Code: 43119.
[nQSError: 43119] Query Failed:(HY000)
State: HY000. Code: 77031. [nQSError: 77031]
Error occurs while calling remote service DatasourceService. Details: [JDSError : 115]
Database Error - Cause: SQL compilation error: error line 2 at position 19
invalid identifier 'T1000001.ASSOCID'
Action: Please refer to logs for more Details (HY000)
Reading this error message, I see that it happened when calling the remote data source service. The error was triggered by the identifier 'T1000001.ASSOCID'. Looking at this value, I notice two things:
it has the column name in all caps.
it has an alias for the table name, T1000001.
I hadn’t defined an alias for the table name, but I recognized the format of this alias as a machine generated alias. Therefore, the error is not in my SQL but in the SQL generated by OAC.
Off to the forums
When I run into an error which stumps me, I check the forums, in this case, community.oracle.com. I start by looking for posts where people had a similar error. In this case, I didn’t find anything so I moved to the next step of asking my own question. To get a good answer in a forum, it’s critical to include as much relevant context as possible, in a concise way. To do this, I go back through every step of the process and document it carefully, taking screenshots and copying the text of error messages. Error messages in text are very powerful because someone who knows how to look can find them, even if I couldn’t. If it’s not in text, someone has to extract it, usually by hand copying.
In the process of documenting my process, I looked back at my connection. To change the setting, you need to right click and Inspect. On the General tab, I saw the connection details and credentials. I also saw two other tabs: Advanced and Access. Under the Advanced tab is a single option for Quoting Identifiers with a dropdown with one value: ‘Double Quotes (“ “)’. I turned this setting on and went back to my data model, which now loaded a preview without error. I saw that it worked with my test case of SELECT “OneMixedCaseColumn” FROM <server.schema.table>. I also wanted to see if it would also work with my original statement of SELECT * FROM <server.schema.table>, and it did.
Even though I figured it out myself, I posted the problem and solution in the forum. This will help my future self if I run into a similar problem in the future. It helps other people who run into a similar issue. It also builds community. Trying to figure out technical issues can be frustrating and lonely, and posting in the forums makes the world a bit less lonely.
Bonus: why is this designed this way?
I don’t know. For a long time, best practice naming conventions for SQL recommended single case names with underscores to separate words. Oracle databases are case insensitive. Even Snowflake is case insensitive by default. The rise of self-service data tools has increased the use of human readable names using Pascal case or other conventions. Putting the setting for quotes on the Advanced tab creates a space in the application for other options that may eventually be needed for compatibility with different databases. Although the only current option for delimited identifiers is double quotes, there are databases which use other options. Using a dropdown could enable other options to be added later.
I’m an Oracle employee, but opinions are my own.