ORA-00904: invalid identifier
A column or other name in the statement doesn't exist or isn't visible there: a typo, a mixed-case quoted name, a reserved word, or an alias used too early.
- Error code
ORA-00904- Category
- Names and objects
What it means
ORA-00904 means Oracle couldn't resolve a name in the statement, almost always a column. The message includes the name it couldn't resolve, for example ORA-00904: "EMAIL_ADRESS": invalid identifier. Oracle stores unquoted names in upper case, so a column created as "Email" can only be referenced as "Email".
Newer releases word it as identifier: invalid identifier with the same name in front.
Common causes
- A misspelled column name, or a column that belongs to a different table in the query.
- A column created with a quoted mixed-case or lower-case name, referenced without quotes.
- Using a column alias in
WHEREorGROUP BYof the same query level; aliases are only visible inORDER BY. - Using a reserved word as a column name without quoting it.
- Referencing a column of an outer table from too deep inside nested subqueries in older releases.
How to fix it
-
List the table's real column names.
SELECT column_name FROM all_tab_columns WHERE owner = 'HR' AND table_name = 'EMPLOYEES' ORDER BY column_id;
-
Repeat the expression instead of the alias, or wrap the query so the alias exists.
SELECT * FROM (SELECT salary * 12 AS annual FROM hr.employees) WHERE annual > 100000;
-
Quote names that were created in mixed case, exactly as created.
SELECT "Email" FROM customers;
Related errors
ORA-00942table or view does not existORA-00918column ambiguously definedORA-01747invalid user.table.column specification207SQL Server: Invalid column name42703PostgreSQL: column does not exist1054MySQL: Unknown column in field list
More names and objects errors
The code and message text are from the Oracle Database Error Help. Browse the full Oracle error list or search every database error.
Work on Oracle in SQLly
A native SQL client for macOS, Windows and Linux with schema-aware IntelliSense, guardrails for risky statements and clear error messages, for Oracle and the other databases you use.