ORA-00904: invalid identifier

ORA-00904: "EMPLOYEE_NAME": invalid identifier

Oracle could not find a column with that name in the tables the query can see at that point.

Lab-verifiedReproduced and fixed on Oracle AI Database 26ai Free 23.26.3.0.0 · 2026-11-11

ORA-00904 means a name in your SQL does not resolve to any column. The message shows the name exactly as Oracle looked it up, already converted to uppercase, for example "EMPLOYEE_NAME" or "O"."CUST_NAME".

That quoted name is the fastest clue. If it has a prefix, the prefix is the table alias Oracle searched. If the case differs from what you expected, the column was probably created with double quotes.

Causes and fixes

  1. The column name is misspelled or does not exist
  2. A column alias from the SELECT list is used in WHERE
  3. The column was created with a quoted, mixed-case name
  4. In a join, the column is prefixed with the wrong table alias

1The column name is misspelled or does not exist

The table has EMP_NAME, but the query asks for employee_name. The asterisk under the statement points to the exact word Oracle rejected.

List the real columns from USER_TAB_COLUMNS (table name in uppercase) and correct the query.

Setup
Setuprun as lab_a
CREATE TABLE employees (emp_id NUMBER PRIMARY KEY, emp_name VARCHAR2(50), salary NUMBER);
INSERT INTO employees VALUES (1, 'Kim', 3000);
COMMIT;
OUTPUT
Table created.


1 row created.


Commit complete.
Reproducerun as lab_a
SELECT emp_id, employee_name FROM employees;
OUTPUT
SELECT emp_id, employee_name FROM employees
               *
ERROR at line 1:
ORA-00904: "EMPLOYEE_NAME": invalid identifier
Help: https://docs.oracle.com/error-help/db/ora-00904/
Checkrun as lab_a
SELECT column_name FROM user_tab_columns WHERE table_name = 'EMPLOYEES' ORDER BY column_id;
OUTPUT
COLUMN_NAME
------------------------------
EMP_ID
EMP_NAME
SALARY

3 rows selected.

For a table in another schema, use ALL_TAB_COLUMNS with an OWNER condition.

After the fixrun as lab_a
SELECT emp_id, emp_name FROM employees;
OUTPUT
    EMP_ID EMP_NAME
---------- --------------------------------------------------
         1 Kim

1 row selected.

2A column alias from the SELECT list is used in WHERE

annual_salary is an alias defined in the SELECT list. WHERE is evaluated before the SELECT list, so the alias does not exist yet there, and Oracle reports it as an invalid identifier (error at line 3).

Two fixes were run here and both return the same row: wrap the query in an inline view and filter on the alias outside, or repeat the expression in WHERE.

Setup
Setuprun as lab_a
CREATE TABLE employees (emp_id NUMBER PRIMARY KEY, emp_name VARCHAR2(50), salary NUMBER);
INSERT INTO employees VALUES (1, 'Kim', 3000);
INSERT INTO employees VALUES (2, 'Lee', 5000);
COMMIT;
OUTPUT
Table created.


1 row created.


1 row created.


Commit complete.
Reproducerun as lab_a
SELECT emp_name, salary * 12 AS annual_salary
  FROM employees
 WHERE annual_salary > 40000;
OUTPUT
 WHERE annual_salary > 40000
       *
ERROR at line 3:
ORA-00904: "ANNUAL_SALARY": invalid identifier
Help: https://docs.oracle.com/error-help/db/ora-00904/
Fixrun as lab_a
SELECT emp_name, annual_salary
  FROM (SELECT emp_name, salary * 12 AS annual_salary FROM employees)
 WHERE annual_salary > 40000;
OUTPUT
EMP_NAME                                           ANNUAL_SALARY
-------------------------------------------------- -------------
Lee                                                        60000

1 row selected.

The fix step shows the inline view; the retry shows the repeated expression salary * 12. The inline view is easier to maintain when the expression is long.

After the fixrun as lab_a
SELECT emp_name, salary * 12 AS annual_salary
  FROM employees
 WHERE salary * 12 > 40000;
OUTPUT
EMP_NAME                                           ANNUAL_SALARY
-------------------------------------------------- -------------
Lee                                                        60000

1 row selected.

3The column was created with a quoted, mixed-case name

The column was created as "userName" in double quotes, so it is stored in mixed case. Without quotes, userName is converted to USERNAME, which is not the same name.

USER_TAB_COLUMNS shows the stored name userName. Tables created by ORMs, migration tools or other databases often have names like this.

Setup
Setuprun as lab_a
CREATE TABLE members (id NUMBER PRIMARY KEY, "userName" VARCHAR2(50));
INSERT INTO members VALUES (1, 'kim');
COMMIT;
OUTPUT
Table created.


1 row created.


Commit complete.
Reproducerun as lab_a
SELECT id, userName FROM members;
OUTPUT
SELECT id, userName FROM members
           *
ERROR at line 1:
ORA-00904: "USERNAME": invalid identifier
Help: https://docs.oracle.com/error-help/db/ora-00904/
Checkrun as lab_a
SELECT column_name FROM user_tab_columns WHERE table_name = 'MEMBERS' ORDER BY column_id;
OUTPUT
COLUMN_NAME
------------------------------
ID
userName

2 rows selected.

Write the column in double quotes with exactly the same case: "userName".

After the fixrun as lab_a
SELECT id, "userName" FROM members;
OUTPUT
        ID userName
---------- ------------------------------
         1 kim

1 row selected.

4In a join, the column is prefixed with the wrong table alias

CUST_NAME is in CUSTOMERS (alias c), but the query writes o.cust_name, so Oracle looks in ORDERS. The message shows this: "O"."CUST_NAME".

Search USER_TAB_COLUMNS by column name to see which table really has it, then fix the alias.

Setup
Setuprun as lab_a
CREATE TABLE customers (cust_id NUMBER PRIMARY KEY, cust_name VARCHAR2(50));
CREATE TABLE orders (order_id NUMBER PRIMARY KEY, cust_id NUMBER, amount NUMBER);
INSERT INTO customers VALUES (1, 'Kim');
INSERT INTO orders VALUES (10, 1, 500);
COMMIT;
OUTPUT
Table created.


Table created.


1 row created.


1 row created.


Commit complete.
Reproducerun as lab_a
SELECT o.order_id, o.cust_name, o.amount
  FROM orders o
  JOIN customers c ON c.cust_id = o.cust_id;
OUTPUT
SELECT o.order_id, o.cust_name, o.amount
                   *
ERROR at line 1:
ORA-00904: "O"."CUST_NAME": invalid identifier
Help: https://docs.oracle.com/error-help/db/ora-00904/
Checkrun as lab_a
SELECT table_name, column_name FROM user_tab_columns WHERE column_name = 'CUST_NAME';
OUTPUT
TABLE_NAME                     COLUMN_NAME
------------------------------ ------------------------------
CUSTOMERS                      CUST_NAME

1 row selected.
After the fixrun as lab_a
SELECT o.order_id, c.cust_name, o.amount
  FROM orders o
  JOIN customers c ON c.cust_id = o.cust_id;
OUTPUT
  ORDER_ID CUST_NAME                                              AMOUNT
---------- -------------------------------------------------- ----------
        10 Kim                                                       500

1 row selected.

FAQ

The column exists. Why do I still get ORA-00904?

Check the name in the error message. If it has an alias prefix like "O"., you are looking in the wrong table. If the stored column name is mixed case in USER_TAB_COLUMNS, it must be written in double quotes with the same case.

Can I use a column alias in the WHERE clause in Oracle?

No. WHERE runs before the SELECT list, so the alias is unknown there and you get ORA-00904. Use an inline view and filter outside it, or repeat the expression in WHERE.

How do I list the column names of a table?

Run SELECT column_name FROM user_tab_columns WHERE table_name = 'EMPLOYEES' ORDER BY column_id; with the table name in uppercase. For tables in other schemas, use ALL_TAB_COLUMNS and add owner = 'SCHEMA'.

How we verify

Each scenario starts from fresh database users. A script runs the setup, confirms the exact error code, applies the fix and runs the statement again. The output shown is what the database printed, saved by the script.

lab_a and lab_b are ordinary users created for the test; system is the DBA account. Oracle AI Database 26ai Free Release 23.26.3.0.0 - Develop, Learn, and Run for Free

Related errors