Ask the Expert

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!

    Requires Free Membership to View

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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: