Oracle: ORA-00904 Error Message

ORA-00904 Error Message

Learn the cause and how to resolve the ORA-00904 error message in Oracle.

Description

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.

Resolution

The option(s) to resolve this Oracle error are:

Option #1

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.
• The column name can not be longer than 30 characters.
• The column name 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.
• The 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 contact_id AS "c_id", last_name, first_name
  2  FROM contacts
  3  ORDER BY "cid";
ORDER BY "cid"
         *
ERROR at line 3:
ORA-00904: "cid": invalid identifier

This error was created by aliasing a column, but then mistyping the alias later. In this example, we created the alias called "c_id" for the contact_id, but then called it as "cid" 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 clause as follows:

SQL> SELECT contact_id AS "c_id", last_name, first_name
  2  FROM contacts
  3  ORDER BY "c_id";

10 rows selected