How to fix Oracle ORA-00904 Error Message

Description or the error

When you encounter an ORA-00904 error, the following error message will appear:

• ORA-00904: invalid identifier

Cause

You tried to execute a SQL statement that included an invalid column name or the column name is missing. This commonly occurs when you reference an invalid alias in a SELECT statement.

Solution

The option(s) to resolve this Oracle error are: Rewrite your SQL to include a valid column name. To be a valid column name the following criteria must be met:

• the column name must begin with a letter.
• column name can not be longer than 30 characters.
• name of the column must be made up of alphanumeric characters or the following special characters: $, _, and #. If the column name uses any other characters, it must be enclosed in double quotation marks.
• column name can not be a reserved word.

Let's look at an example of how to resolve an ORA-00904 error. For example, if you ran the following SELECT statement, you would receive an ORA-00904 error:

SQL SELECT name AS "name_id", age,
  2  FROM users
  3  ORDER BY "nameid";
ORDER BY "nameid"
         *
ERROR at line 3:
ORA-00904: "nameid": invalid identifier

This error was created by aliasing a column, but then mistyping the alias later. In this example, we created the alias called "name_id" for the name, but then called it as "nameid" in the ORDER BY clause. To resolve this error, we can modify our SELECT statement to use the correct alias name in the ORDER BY:

SQL SELECT name_id AS "name_id", age
  2  FROM users
  3  ORDER BY "name_id";