DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-00904 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-00904: Invalid Identifier — Causes, Fixes, and Prevention

ORA-00904 is one of the most common Oracle SQL errors, occurring when Oracle's SQL parser encounters a column name, alias, or identifier it cannot recognize. This error is thrown at parse time, before any data is accessed, and the error message typically includes the offending identifier to help you pinpoint the issue quickly.

Top 3 Causes and Fixes

1. Typo or Non-Existent Column Name

The most frequent cause is simply a misspelled column name or referencing a column that doesn't exist in the table — often after a schema change removed or renamed the column.

-- Error: typo in column name
SELECT EMPLOYE_ID, FIRST_NAME FROM EMPLOYEES;
-- ORA-00904: "EMPLOYE_ID": invalid identifier

-- Fix: verify the exact column name first
SELECT COLUMN_NAME FROM USER_TAB_COLUMNS
WHERE TABLE_NAME = 'EMPLOYEES';

-- Corrected query
SELECT EMPLOYEE_ID, FIRST_NAME FROM EMPLOYEES;
Enter fullscreen mode Exit fullscreen mode

2. Referencing a SELECT Alias in WHERE or HAVING

Oracle processes clauses in a specific logical order: FROM → WHERE → GROUP BY → HAVING → SELECT. Because WHERE is evaluated before SELECT, aliases defined in the SELECT clause are not yet visible when WHERE is processed.

-- Error: alias used in WHERE clause
SELECT EMPLOYEE_ID, SALARY * 12 AS ANNUAL_SAL
FROM EMPLOYEES
WHERE ANNUAL_SAL > 60000;
-- ORA-00904: "ANNUAL_SAL": invalid identifier

-- Fix 1: use a subquery
SELECT EMPLOYEE_ID, ANNUAL_SAL
FROM (
    SELECT EMPLOYEE_ID, SALARY * 12 AS ANNUAL_SAL
    FROM EMPLOYEES
)
WHERE ANNUAL_SAL > 60000;

-- Fix 2: repeat the expression in WHERE
SELECT EMPLOYEE_ID, SALARY * 12 AS ANNUAL_SAL
FROM EMPLOYEES
WHERE SALARY * 12 > 60000;
Enter fullscreen mode Exit fullscreen mode

3. Using an Oracle Reserved Word as an Identifier

Oracle has hundreds of reserved words (LEVEL, DATE, NUMBER, COMMENT, etc.). Using them as column or table names without double quotes causes ORA-00904.

-- Error: LEVEL is a reserved word for hierarchical queries
SELECT ID, LEVEL FROM MY_TABLE;
-- ORA-00904: "LEVEL": invalid identifier

-- Fix: wrap the reserved word in double quotes
SELECT ID, "LEVEL" FROM MY_TABLE;

-- Check if a word is reserved
SELECT KEYWORD, RESERVED FROM V$RESERVED_WORDS
WHERE KEYWORD = 'LEVEL';

-- Best practice: avoid reserved words at design time
CREATE TABLE MY_TABLE (
    ID          NUMBER,
    GRADE_LEVEL VARCHAR2(20),  -- instead of "LEVEL"
    REG_DATE    DATE           -- instead of "DATE"
);
Enter fullscreen mode Exit fullscreen mode

Quick Diagnosis Checklist 

-- 1. Confirm exact column names
SELECT COLUMN_NAME, DATA_TYPE
FROM USER_TAB_COLUMNS
WHERE TABLE_NAME = 'YOUR_TABLE'
ORDER BY COLUMN_ID;

-- 2. Check table/view existence
SELECT OBJECT_NAME, OBJECT_TYPE
FROM USER_OBJECTS
WHERE OBJECT_NAME = 'YOUR_TABLE';

-- 3. Review view definition if querying a view
SELECT TEXT FROM USER_VIEWS
WHERE VIEW_NAME = 'YOUR_VIEW';
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

  • Use a DB-aware IDE: Tools like SQL Developer, DBeaver, or Toad provide column name auto-completion, dramatically reducing typos before execution.
  • Establish a Naming Convention: Define standards that avoid Oracle reserved words from the start — use suffixes like _ID, _DT, _CD, _NM to keep column names unique and safe. Always reference V$RESERVED_WORDS when naming new columns or tables.

Related Errors

Error Code Description
ORA-00942 Table or view does not exist
ORA-01747 Invalid column specification
ORA-00923 FROM keyword not found where expected

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.

Top comments (0)