tobspr tobspr - 4 months ago 17
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?

Answer

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 .

Comments