Oracle ORA-00904: Invalid Identifier (Fix Guide)

The Error Message

Oracle raises ORA-00904 when a column name (or other identifier) in your statement does not match any column the parser can resolve. The name inside the quotes in the error message is exactly what Oracle looked for — and failed to find. That detail matters: if you wrote createdAt unquoted and the error says "CREATEDAT", Oracle uppercased your identifier before searching, which is your first clue.

What Causes ORA-00904?

The parser resolved your table successfully (otherwise you would get ORA-00942: table or view does not exist) but could not match one of the column names against that table's definition. Five causes account for nearly every occurrence.

Common Causes (Ranked)

Quick Diagnosis

Before changing any SQL, ask Oracle what the column is actually called. The data dictionary never lies:

-- List the real column names of your table
-- (table_name is stored UPPERCASE unless created quoted)
SELECT column_name, data_type
FROM   user_tab_columns
WHERE  table_name = 'EMPLOYEES'
ORDER  BY column_id;

-- Table in another schema? Use all_tab_columns:
SELECT owner, column_name
FROM   all_tab_columns
WHERE  table_name = 'EMPLOYEES'
AND    owner = 'HR';

-- Find which tables actually contain the column you want:
SELECT table_name, column_name
FROM   all_tab_columns
WHERE  column_name = UPPER('customer_id');

If column_name comes back in anything other than pure UPPERCASE (e.g. createdAt), the column was created with quotes and you must quote it in every query.

How to Fix It

Fix 1: Correct the spelling

Compare your query against the user_tab_columns output above. Watch for singular vs plural and underscores.

-- BAD: column is customer_id, not custid
SELECT custid, total FROM orders;
-- ORA-00904: "CUSTID": invalid identifier

-- FIXED:
SELECT customer_id, total FROM orders;

Fix 2: Match the case of quoted identifiers exactly

This is the trap that bites anyone whose schema was generated by a tool (Liquibase, an ORM, a SQL Server migration script) that double-quoted mixed-case names.

-- The table was created like this:
CREATE TABLE events ("eventType" VARCHAR2(50), "createdAt" DATE);

-- BAD: unquoted names get uppercased to EVENTTYPE / CREATEDAT
SELECT eventType, createdAt FROM events;
-- ORA-00904: "CREATEDAT": invalid identifier

-- FIXED: quote them, exact case
SELECT "eventType", "createdAt" FROM events;

-- BETTER long-term: rename to unquoted (case-insensitive) names
ALTER TABLE events RENAME COLUMN "createdAt" TO created_at;

The same lowercasing pitfall exists in PostgreSQL — see PostgreSQL 42703: column does not exist — except PostgreSQL folds unquoted identifiers to lowercase while Oracle folds them to uppercase.

Fix 3: Don't reference SELECT aliases in WHERE

WHERE runs before SELECT, so the alias does not exist yet. Repeat the expression or push the query into a subquery.

-- BAD:
SELECT salary * 12 AS annual_salary
FROM   employees
WHERE  annual_salary > 100000;
-- ORA-00904: "ANNUAL_SALARY": invalid identifier

-- FIXED (repeat the expression):
SELECT salary * 12 AS annual_salary
FROM   employees
WHERE  salary * 12 > 100000;

-- FIXED (subquery — alias is now a real column to the outer query):
SELECT *
FROM  (SELECT salary * 12 AS annual_salary FROM employees)
WHERE annual_salary > 100000;

Note: ORDER BY annual_salary works fine — ORDER BY is evaluated after SELECT.

Fix 4: Check the right environment and table

If the column "definitely exists", confirm it exists here — same database, same schema, same table your query joins.

-- Which schema am I in, which table am I really hitting?
SELECT sys_context('USERENV', 'CURRENT_SCHEMA') FROM dual;

-- The column may be on the joined table, not the one you prefixed:
-- BAD:
SELECT o.order_id, o.shipped_date
FROM   orders o JOIN shipments s ON s.order_id = o.order_id;
-- ORA-00904: "O"."SHIPPED_DATE": invalid identifier

-- FIXED: shipped_date lives on shipments
SELECT o.order_id, s.shipped_date
FROM   orders o JOIN shipments s ON s.order_id = o.order_id;

Fix 5: Avoid reserved words at CREATE time

-- BAD: NUMBER is a reserved word
CREATE TABLE tickets (number NUMBER, status VARCHAR2(20));
-- ORA-00904: invalid identifier

-- FIXED: pick a non-reserved name
CREATE TABLE tickets (ticket_number NUMBER, status VARCHAR2(20));

-- Check whether a word is reserved:
SELECT keyword, reserved FROM v$reserved_words WHERE keyword = 'NUMBER';

ORM and Tooling Gotchas

If your application code rather than hand-written SQL hits ORA-00904, look here first:

• Hibernate/JPA naming strategy: Java entities with camelCase fields map to created_at with the default implicit naming strategy, but a custom PhysicalNamingStrategy (or @Column(name="\"createdAt\"")) can produce quoted mixed-case columns. Any native query against those columns then needs exact-case quotes.
• Cross-database migrations: schemas exported from SQL Server or PostgreSQL often arrive with quoted lowercase identifiers ("id", "name"). Every ad-hoc Oracle query against them must quote — most teams rename to unquoted identifiers once and end the pain.
• Old-style joins: the (+) outer-join operator and ANSI joins resolve column scope differently; converting between them can move a column out of scope and surface ORA-00904 in a query that "used to work".

How to Prevent This Error

Never create quoted mixed-case identifiers in Oracle — let everything fold to uppercase and write queries case-insensitively. Verify column names against user_tab_columns instead of guessing. Keep dev and prod schemas in sync with versioned migrations so "missing column" surprises don't reach production. If a number-conversion error appears next, see ORA-01722: invalid number — it is the usual follow-up once identifiers resolve but datatypes don't match.

Fix Oracle Errors with AI2SQL

Instead of cross-checking the data dictionary by hand, connect your schema and describe what you need in plain English — AI2SQL generates Oracle SQL with the correct, existing column names and quoting.

Frequently Asked Questions