It's been many years since I last worked with Oracle in anger, and having had to recently work with it again it's amazing how many of its "features" (I use the term guardedly) have either appeared in the intervening years, or (more likely) I had forgotten about altogether
However this one had me (and a few others in the office, I might add) puzzled for an afternoon, and once again the documentation for this is scant (or at the very least, hard to find).
Consider the following table:
CREATE TABLE A_TABLE ( A_COLUMN NUMBER(5,0) NOT NULL ENABLE )
Now take a look at he following queries:
SELECT A_COLUMN FROM A_TABLE -- Returns the data SELECT A_Column FROM A_TABLE -- Returns the data
Nothing magical there. The query returns the data correctly regardless of the case used in the query, exactly as we would expect.
Now consider this table:
CREATE TABLE ANOTHER_TABLE ( "A_Column" NUMBER(5,0) NOT NULL ENABLE )
When we query this table in the same way, the result is very different:
SELECT A_COLUMN FROM ANOTHER_TABLE -- ORA-00904: "A_COLUMN": invalid identifier SELECT A_Column FROM ANOTHER_TABLE -- ORA-00904: "A_COLUMN": invalid identifier
As you can see Oracle now complains that it can't find the specified column, even when we have specified the column name with the correct case.
The problem is caused by the fact that, not only is Oracle case-sensitive, but it also implicitly converts all identifiers to UPPER CASE. This is why, when querying
A_TABLE, the case of the query doesn't matter. However, when querying
ANOTHER_TABLE, the query fails as the identifier is always converted to
A_COLUMN, whereas the column is actually named
To prevent Oracle converting identifiers to upper case, they must be enclosed in double-quotes ('"'), just as when the table was created. Therefore, the following query will work:
SELECT "A_Column" FROM ANOTHER_TABLE
OK, fair enough, it's not ideal but we can live with that. However, this in itself presents another interesting scenario. Consider this table:
CREATE TABLE YET_ANOTHER_TABLE ( "A_COLUMN" NUMBER(5,0) NOT NULL ENABLE, "A_Column" VARCHAR2(100) NOT NULL ENABLE )
Yes, in Oracle this is a perfectly valid (if not recommended) table definition! So assuming the following data, what happens when we query it:
SELECT A_COLUMN FROM YET_ANOTHER_TABLE -- Returns 1050, 2060 and 3070 SELECT "A_COLUMN" FROM YET_ANOTHER_TABLE -- Returns 1050, 2060 and 3070 SELECT A_Column FROM YET_ANOTHER_TABLE -- Returns 1050, 2060 and 3070 SELECT "A_Column" FROM YET_ANOTHER_TABLE -- Returns Foo, Bar and Baz
The Moral of the Story
This clearly highlights the need for robust database standards, part of which must include whether or not to use quoted identifiers and follow that decision rigidly throughout your database. My personal recommendation would be against the use quoted identifiers as they appear to cause more problems and confusion than they are worth; not least the potential creation of "duplicate" column names.