How to Create Oracle Tables without using quotes in column names
I'm noticing that in Dataiku's default table creation SQL statements (for Dataiku-managed datasets), quotes are used around column names by default. My organization is accustomed to not using double quotes around column names, so that in our external SQL tool, we may query columns without having to worry about case sensitivity. What's your recommendation so that our SQL developers can continue to query Dataiku-managed tables in a case-insensitive way?
Best Answer
-
Hi,
In order to guarantee compatibility with all systems and preserving your column names, we indeed use quoted characters, which means that the columns are created in the database truly as they are, and do not get mangled by the database.
However, since Oracle is a "uppercase default" database:
- If your source data is already in Oracle, it will already have uppercase column names, and the fact that they are quoted in Dataiku will not change anything: the columns will still be truly uppercase in the database, and you can freely choose not to quote them in your own queries
- If your source data is not already in Oracle, and contains lowercase or mixed case column names, you can use the prepare recipe to load it into Oracle, and use "Column View > Select all > Rename > to upper case", in order to rename all columns to upper case, for easy processing in Oracle
Hope this helps
