Q

SQL execution error ORA-00904

I'm receiving the SQL execution error "ORA-00904: invalid column name." I have checked all the names and they are correct. I checked the SQL string and it is working in TOAD, which means it retrieved the data. I am lost!

This Content Component encountered an error
I'm receiving the SQL execution error "ORA-00904: invalid column name." I have checked all the names and they are correct. I checked the SQL string and it is working in TOAD, which means it retrieved the data. I am lost!
One thing that trips people up is the case sensitivity of Oracle table and column names. For most situations, you can specify table and column names without paying attention to the case sensitivity. But if the table was created with double quotes around the table or column name, then the table or column name must be specified with case sensitivity in mind. For example, assume I create the table as follows:
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

Dig deeper on Oracle database design and architecture

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

1 comment

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close