CREATE TABLE t1 ( "Id" NUMBER, "Val" VARCHAR2(10));Since I put the column names in double quotes, I must match case when referring to these columns. The following query will fail with the ORA-904 error you mentioned above:
SELECT Id FROM t1;The problem here is that Oracle first converts the column name to upper case wich does not match the name I specified in the CREATE TABLE statement. To get around this problem, I must put the column name in double quotes:
SELECT "Id" FROM t1;Let's look at another example. I create a table as follows:
CREATE TABLE t2 ( id NUMBER);Now if I query this table as follows, I will receive the ORA-904 error:
SELECT "id" FROM t2;In this example, the table was created without double quotes around the column name. When I put double quotes in my query, I am instructing Oracle to look for a column with that exact spelling. But Oracle converted the column name to upper case when the table was created, so the column is not found.
This was first published in November 2005