tobspr tobspr - 8 months ago 57
SQL Question

Oracle DB quote column names

When using regular tables, its fine to use the following Oracle SQL query:

SELECT max(some_primary_key) FROM MyTable

However, when using Database Objects (i.e. a table of an object), this yields to the following error:

ORA-00904: "SOME_PRIMARY_KEY": invalid identifier

When quoting the column name, like this:

SELECT max("some_primary_key") FROM MyTable

This works like expected. Why is it necessary to escape column names when working with Objects, but not with Tables?


It doesn't have to do with objects or tables, it has to do with how these objects/tables have been created.

If you do create table "blabla" then you always need to address this table with "blabla", if you do create table blabla then you can address this table via BLABLA or blabla or bLabLa. Using " " makes the name case sensitive and that is the reason why most developers don't use " " because usually you don't want case sensitive names .