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.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.
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.