Solving ORA-00904: Invalid identifier Oracle Database Error

ORA-00904: Invalid identifier is a common Oracle Database error. It means Oracle doesn’t recognize a column name, alias, or identifier you’re using in your SQL statement. Put simply: you’ve referenced a name the database cannot find or is not allowed to use in that context.

What Triggers ORA-00904

This error happens when:

1. The column doesn’t exist
   • You misspelled the column name.
   • You’re selecting a column that isn’t in the table.
2. Wrong alias or scope
   • You used a column alias in the same SELECT before it’s defined.
   • You referenced an alias in the WHERE or GROUP BY clause (Oracle doesn’t allow this in WHERE).
3. Case sensitivity or quotes issue
   • You created a column or alias using double quotes with mixed case, e.g., "FirstName", but later refer to it as firstname. Oracle treats "FirstName" and FIRSTNAME differently.
4. Permissions or invalid object reference
   • You referenced a column in a view or synonym that your user doesn’t have privileges to access.
5. Using reserved keywords as column names without quoting.

Real-Life Examples

Example 1: Misspelled Column

-- Table employees has column "last_name"
SELECT first_name, lastname
FROM employees;

Error: ORA-00904: "LASTNAME": invalid identifier
Fix: Correct the spelling:

SELECT first_name, last_name
FROM employees;

Example 2: Alias Used in WHERE Clause

SELECT salary * 12 AS annual_salary
FROM employees
WHERE annual_salary > 60000; -- ❌ invalid here

Oracle doesn’t allow annual_salary in WHERE because aliases are assigned after WHERE is evaluated.

Fix: Repeat the calculation or wrap in a subquery:

-- Option 1: Repeat calculation
SELECT salary * 12 AS annual_salary
FROM employees
WHERE salary * 12 > 60000;

-- Option 2: Use subquery
SELECT *
FROM (SELECT salary * 12 AS annual_salary FROM employees)
WHERE annual_salary > 60000;

Example 3: Quoting and Case Sensitivity

-- Column was created with mixed case and quotes
CREATE TABLE staff ("FirstName" VARCHAR2(20));

-- Using wrong case
SELECT firstname FROM staff; -- ❌ invalid identifier

Fix: Use the exact case or avoid quotes:

SELECT "FirstName" FROM staff; -- ✅ correct

Or recreate the column without quotes:

CREATE TABLE staff (firstname VARCHAR2(20));

Example 4: Column Doesn’t Exist in View

SELECT department_name, location
FROM dept_view; -- But dept_view doesn’t have "location"

Fix: Check the view’s definition:

DESC dept_view;

Use the correct column:

SELECT department_name, location_id
FROM dept_view;

How to Solve ORA-00904

1. Check spelling: Run DESC table_name; or query ALL_TAB_COLUMNS to confirm the exact column names.
2. Verify aliases: Use aliases only in SELECT or ORDER BY, or wrap your query in a subquery.
3. Check privileges: Ensure your user can access the table/view.
4. Avoid reserved keywords: If necessary, wrap them in quotes " ".
5. Be careful with case sensitivity: Avoid creating objects with quoted mixed-case identifiers.

✅ Key Takeaway (Simple Technical Terms)

The database is saying:

“I don’t recognize the name you gave me—it’s either misspelled, out of place, or doesn’t exist here.”

By double-checking your spelling, aliases, and table definitions, and by avoiding reserved keywords or quoted identifiers, you can quickly fix ORA-00904 and prevent it in future queries.