Simple Ways to Solve Ora-00904 Invalid Identifier Error

1. Introduction

In this article, we’ll discuss about the root cause and solution for the error ORA-00904 Invalid Identifier error.

2. Ora-00904 Invalid Identifier – Cause

The error commonly appears when we use the identifies such as column names which are not existing.

3. Solution

3.1. ORA-00904 in CREATE TABLE Statements

The error occurs when we use any invalid names/letters or using an Oracle reserved keywords in column names while creating table.

In the below create table statement, Comment is Oracle reserved keyword and this will cause Invalid identifier error when executed.

CREATE TABLE test 
  ( 
     id      NUMBER, 
     name    VARCHAR2(200), 
     COMMENT VARCHAR2(4000) 
  ); 

3.2. ORA-00904 in ALTER TABLE Statements

The error occurs when we try to modify a non-existent column or add a column with the reserved word as a column name. Also, this can occur if we use the keyword column while adding column as explained below:

ALTER TABLE test 
  ADD COLUMN date_of_birth DATE; 

“Column” word is not necessary as mentioned above. A column can be simply added as below.

ALTER TABLE test 
  ADD date_of_birth DATE; 

3.3. ORA-00904 in SELECT or INSERT or update Statements

The error occurs during select or insert or update when we refer to an invalid or non-existent column during the DML operations.

In the below update statement, dept_id column is invalid in the test table but we try to update it. This will cause Invalid Identifier error.

UPDATE test 
SET    dept_id=1 
WHERE  id=101;

The column names used in the DML operation query should be a valid column to avoid this error.

3.4. ORA-00904 in PL/SQL Stored Procedures

When it comes to PL/SQL, Invalid identifier error occurs when we try to use any invalid column in the SQL query or when we refer to a variable which is not declared in the PL/SQL program.

4. Conclusion

In this article, we have seen the reasons and the solution for Ora-00904 Invalid Identifier error.

If you still have questions, feel free to ask in our SQL forum.