ORA-00904 Invalid Identifier — Typo and Case Fix

ORA-00904: "ORDERSTATUS": invalid identifier. It appears the moment you run the statement — no partial results, no rows, just a parse-time rejection. Usually it follows a deploy (a renamed column), a handoff (someone's query against your schema), or a late night (a typo in the third join). The maddening part is that the name looks right until you compare it against the dictionary character by character.

Oracle validates every identifier against the data dictionary before executing anything, and its rules surprise newcomers: unquoted names fold to UPPERCASE, quoted names preserve exact case, SELECT aliases are invisible to WHERE at the same level, and a few dozen reserved words can't serve as bare columns at all.

This guide walks the resolution order: dictionary check, typo versus rename triage, quoted-case matching, alias-scope repair, and reserved-word handling — plus a CI gate that parses report SQL against the dictionary before it ships. Keep this guide bookmarked: the resolution order below compresses most 00904 incidents into a ten-minute dictionary exercise.

ORA-00904 Fails at Parse Time

Internalize this and half the debugging folklore dies: 00904 happens before Oracle reads a single row. No data change, no concurrent session, no grant can cause or cure it — the parser walks your identifiers, consults the dictionary, and rejects the unknown one. That is why retrying never helps, why it reproduces perfectly in SQL Developer, and why the fix is always in the text of the SQL or the state of the schema, never in the server.

The message quotes the failed identifier exactly, case included — "ORDERSTATUS" versus "OrderStatus" is itself a clue about quoting. Copy it verbatim into your dictionary queries rather than retyping from memory; retyping introduces the same class of error you're hunting. And test with fragments: SELECT "OrderStatus" FROM orders WHERE ROWNUM = 1 parses (or fails) in a second, while the 200-line report takes minutes to even read.

Because it's parse-time, EXPLAIN PLAN is a perfect validator: it parses without executing, so EXPLAIN PLAN FOR <query> in CI fails builds on exactly the identifier production would reject. Parse-testing is cheap, deterministic, and complete — there is no 00904 that passes EXPLAIN PLAN and fails at runtime. Make it the default validator.

-- What columns actually exist? (compare character by character)
SELECT column_name, data_type, nullable
FROM all_tab_columns
WHERE table_name = 'ORDERS'
ORDER BY column_id;

-- Table itself present?
SELECT table_name FROM all_tables WHERE table_name = 'ORDERS';

-- Minimal parse test (ROWNUM keeps it instant)
SELECT status FROM orders WHERE ROWNUM = 1;

Typo'd or Renamed Columns After Deploys

The most common story is also the simplest: the query and the schema disagree. Fresh typos (custmoer_id), stale references after RENAME COLUMN, and cross-branch drift (your branch renamed it, main didn't) all produce byte-identical symptoms. Triage by diffing the query's column list against all_tab_columns output: every name must match exactly, and the first mismatch is usually the reported one — Oracle names the first failure, not all of them.

Renames deserve a process, not just a fix. ALTER TABLE ... RENAME COLUMN is instant and safe, but every consumer — app code, reports, ETL, materialized view definitions — must move in the same release or behind a compatibility alias. Search the codebase for the old name (including quoted variants and dynamic SQL strings) before the rename ships; dynamic SQL built by concatenation hides from every static check except a runtime parse test.

For the immediate incident, prefer the compatibility alias over the revert: a view or synonym carrying the old name restores service in minutes without re-migrating the app schema. Reverts re-break the new code that already adopted the rename; aliases let both generations coexist until the slow train catches up.

-- Diff the query's names against reality
SELECT column_name FROM all_tab_columns
WHERE table_name = 'ORDERS';

-- What did the deploy actually rename? (review the migration)
-- ALTER TABLE orders RENAME COLUMN order_status TO status;

-- Compatibility alias: old name keeps working for slow consumers
CREATE OR REPLACE VIEW orders_compat AS
SELECT order_id, customer_id, status AS order_status, total
FROM orders;
-- Point legacy reports at orders_compat until they upgrade.

Quoted Case: "Name" Is Not NAME

Oracle folds unquoted identifiers to UPPERCASE — status, Status, and STATUS all mean STATUS. Double quotes opt out: "OrderStatus" stores mixed case permanently, and from then on only the exact quoted form resolves. Every unquoted reference folds to ORDERSTATUS, misses, and raises 00904 against a column that plainly appears in DESCRIBE output. Tools that auto-quote (some ORMs, GUI designers) manufacture this trap silently.

Detect it with a case-mismatch query: any column_name differing from its UPPER form was created quoted. Prove it with the minimal pair — unquoted fails, quoted succeeds — before changing anything, because the same symptom (exists-but-unresolvable) also comes from scope errors, and the quoted probe distinguishes them cleanly.

Cure it with a rename to unquoted convention, not with permanent quoting. Quoting every reference forever taxes developers, breaks case-insensitive tooling assumptions, and corrupts dump-and-reload scripts that normalize case. One RENAME COLUMN to STATUS plus a code sweep ends the tax; quote-forever pays it daily. Sweep dynamically built SQL too — concatenated column names hide quoted references from every static search. Grep for the column in all casings to be thorough.

-- Which columns were created quoted? (differ from UPPER)
SELECT column_name FROM all_tab_columns
WHERE table_name = 'ORDERS'
  AND column_name <> UPPER(column_name);

-- Prove the trap: unquoted folds and misses...
-- SELECT OrderStatus FROM orders WHERE ROWNUM = 1;  -- ORA-00904
-- ...quoted exact-case hits
SELECT "OrderStatus" FROM orders WHERE ROWNUM = 1;

-- Cure: rename to unquoted convention once
ALTER TABLE orders RENAME COLUMN "OrderStatus" TO status;

Alias Scope: WHERE Can't See SELECT Aliases

SQL evaluates WHERE before SELECT at the same query level, so SELECT amount * 1.2 AS total ... WHERE total > 100 references something that doesn't exist yet — 00904 on a perfectly spelled name. The same blindness covers GROUP BY and HAVING at the same level; ORDER BY, evaluated later, sees aliases fine. This asymmetry confuses everyone exactly once, then becomes muscle memory.

Three repairs, pick by clarity. Repeating the expression in WHERE is simplest for short formulas. Nesting — inner query defines the alias, outer query filters — reads best for complex expressions and works in every clause. A WITH subquery (factored) names the computation once and references it everywhere, which scales when five downstream clauses need the same value.

The sibling scope bug is the wrong table prefix: SELECT o.status FROM orders ord fails because the alias is ord. Alias consistently (short, stable prefixes per table), qualify every column in joins, and the parser stops complaining about names that 'exist' — because now they're addressable. When a join still fails after prefixes look right, comment out tables one by one: the last table removed before parsing succeeds is the scope you broke. Label that join explicitly.

-- Fails: WHERE can't see the same-level alias
-- SELECT amount * 1.2 AS total FROM orders WHERE total > 100;

-- Fix 1: repeat the expression
SELECT amount * 1.2 AS total FROM orders WHERE amount * 1.2 > 100;

-- Fix 2: nest (inner defines, outer filters)
SELECT * FROM (SELECT amount * 1.2 AS total FROM orders)
WHERE total > 100;

-- Fix 3: factor with WITH
WITH priced AS (SELECT amount * 1.2 AS total FROM orders)
SELECT * FROM priced WHERE total > 100;

Reserved Words as Column Names

COMMENT, DATE, NUMBER, SIZE, UID, GROUP, ORDER — Oracle reserves dozens of ordinary-looking words, and a bare reference parses as the keyword, then fails as an identifier (or worse, parses as something unintended). Legacy schemas inherit these from looser eras: a column named comment from 2009 works until the first query that uses it unquoted in a new context.

Check V$RESERVED_WORDS before assuming a typo: one row proves the name was never going to work bare. The stopgap is quoting ("COMMENT") wherever it appears; the cure is renaming to a non-reserved equivalent (customer_comment, order_note) on the next maintenance window. Quoted reserved words technically function but poison every ORM mapping, export script, and new-hire onboarding that touches the table.

Prevent recurrence with a reserved-word lint on DDL: any CREATE TABLE or ALTER ... ADD containing a V$RESERVED_WORDS match fails review. The dictionary already knows the full list — point the linter at it instead of maintaining a stale copy in a wiki page nobody reads. Include ALTER TABLE ... ADD variants in the lint, since most reserved-word columns sneak in through later alters rather than the original CREATE TABLE statement.

-- Is the name reserved? (one row = never works bare)
SELECT keyword, reserved FROM V$RESERVED_WORDS
WHERE keyword = 'COMMENT';

-- Stopgap: quote it everywhere (works, but taxes all future SQL)
SELECT "COMMENT" FROM tickets WHERE ROWNUM = 1;

-- Cure on the next window: rename to a safe equivalent
ALTER TABLE tickets RENAME COLUMN "COMMENT" TO customer_comment;

Prevention: Dictionary-Driven CI

End the class, not the instance. A CI job that runs EXPLAIN PLAN FOR over every report query and migrationAdjacent SELECT fails the build on the exact identifier production would reject — with the rename's own pull request as the culprit, not the 6 AM batch. Keep a shadow schema at the target migration level so the parse test runs against tomorrow's dictionary, not today's.

Add two linters beside it: one rejecting newly quoted mixed-case identifiers in DDL, one rejecting V$RESERVED_WORDS matches as bare columns. Both read from the live dictionary, so they never go stale. Together the three gates (parse test, case lint, keyword lint) cover every 00904 cause except the alias-scope rule — which code review catches because the failing pattern (alias in WHERE) is visually distinctive.

Version your reporting SQL with the schema it targets. A reports package declaring compatibility with migration N gets parse-tested against N; a rename to N+1 without a compatibility alias fails that package's gate loudly. Contracts beat coordination meetings: the schema and its consumers stay in sync because the pipeline enforces it, not because everyone remembers to check every consumer by hand before merging.

#!/bin/bash
# CI gate: every report query must parse against the target schema
set -euo pipefail
CONNECT="app/pass@//db-ci:1521/shop"
FAIL=0
for Q in reports/*.sql; do
  if ! sqlplus -S "$CONNECT" << EOF > /tmp/parse.log 2>&1
WHENEVER SQLERROR EXIT FAILURE
EXPLAIN PLAN FOR $(cat "$Q");
ROLLBACK;
EOF
  then echo "PARSE-FAIL $Q"; grep -i 'ORA-' /tmp/parse.log | head -3; FAIL=1;
  else echo "PARSE-OK $Q";
  fi
done
[ "$FAIL" -eq 0 ] && echo GATE-PASS || (echo GATE-FAIL; exit 1)