This tutorial will explain what is causing the ORA-00904 error to occur and how to resolve the ORA-00904 invalid identifier error in Oracle.

ORA-00904 is a standard error in the oracle database, especially when trying to do CRUD operations in Oracle.

Hint: CRUD : is a kind of (SELECT, INSERT, UPDATE and DELETE) operations that happens in the any database not only Oracle

ORA-00904 Error Cause

This error always occurs when you try to select or reference an invalid or missing column. And as per Oracle Documented, this error causes an action in short sentences.

"ORA-00904: invalid identifier error"
Cause: Column name in error is either missing or invalid.
Action: Enter a valid column name.

ORA-00904 Solution

To resolve this error, firstly, we have to check whether this column name exists in the table or not by running this query:

SELECT TABLE_NAME
      ,COLUMN_NAME
      ,DATA_TYPE
      ,DATA_LENGTH
      ,NULLABLE
FROM   ALL_TAB_COLUMNS
WHERE  TABLE_NAME = 'EMPLOYEES'
AND    COLUMN_NAME = 'MANAGER_ID';

The output of the above query is:

TABLE_NAME	COLUMN_NAME	DATA_TYPE	DATA_LENGTH	NULLABLE
EMPLOYEES	MANAGER_ID	NUMBER	22	Y

In the above query, we checked the existence of our column “MANAGER_ID” from table “EMPLOYEES”. And as we can see from the output, this column exists; otherwise, this query will not return any data, which means no_data_found.

In case the column exists, we have to make sure that the column name meets the following criteria:

• The column name cannot be a reserved word.
• The column name should start with a letter not a number.
• The column name length should not exceed 30 characters.
• The column name could contains one of the following special characters “$” or “_” or “#”. And if we want to include other special characters in the column name we should enclosed the name by double quotation marks like this “COLUMN*1”

ORA-00904: Invalid Identifier Error occurs due to Using a Reserved Word in Column Name

If we use a reserved word as a column name, the ORA-00904 invalid identifier error will occur.

CREATE TABLE OSK_TEST
(
  "COLUMNlelandkrome119"   VARCHAR2 (150)
 ,"COLUMN*2"   VARCHAR2 (150)
 ,LEVEL        VARCHAR2(50)
);

Output:

Error report -
ORA-00904: : invalid identifier

In the above example, the ORA-00904: invalid identifier will occur because we use LEVEL as the third column name, a reserved word in the Oracle database; hence, we can’t use reserved words. Fortunately, we don’t have to remember all reserved words while naming any column in our database table.

Hint: You can refer to Oracle documentation of Oracle Database 10g to find the list of reserved words

ORA-00904: Invalid Identifier Error occur While Inserting Data into Table

This error will occur if we use the wrong column name inside the INSERT statement or use a non-existent column. It happens most of the time because of a typo; however, it could be because someone changed the table structure by changing the column name or deleting the column that we are referencing inside the INSERT statement.

INSERT INTO OSK_TEST (COLUMN1, COLUMN2, LEVEL3)
VALUES      ('FIRST VALUE', 'SECOND VALUE', 'THIRD VALUE');

Output:

Error report -
SQL Error: ORA-00904: "LEVEL3": invalid identifier

We used the non-existence column “LEVEL3” in the above example, so we got this SQL error. We can quickly fix this error by ensuring the column used in the INSERT statements.

ORA-00904: Invalid Identifier Error occur While Select Data from Table

This error will occur if we use the wrong column name inside the SELECT statement or use a non-existent co. Let’s take the below table as an example to execute a select statement against it.

CREATE TABLE OSK_TEST
(
  COLUMN_1   VARCHAR2 (150)
 ,COLUMN_2   VARCHAR2 (150)
);

Now, let’s perform the SELECT statement from this table.

SELECT COLUMN_3 FROM OSK_TEST;

Output:

ORA-00904: "COLUMN_3": invalid identifier
00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action:
Error at Line: 20 Column: 28

We used the non-existence column “COLUMN_3” in the above example, so we got this SQL error.

ORA-00904: Invalid Identifier Error occur While Deleting Data from Table

This error will occur if we use the wrong column name inside the DELETE statement or use a non-existent column name.

Now, let’s perform the DELETE statement against the table name “OSK_TEST”.

DELETE FROM OSK_TEST
WHERE       COLUMN_4 IS NOT NULL;

Output:

Error report -
SQL Error: ORA-00904: "COLUMN_4": invalid identifier
00904. 00000 -  "%s: invalid identifier"

We used the non-existence column “COLUMN_4” in the above example, so we got this SQL error.

ORA-00904: Invalid Identifier Error occur While Updating Data in the Table

If we use the wrong column name inside the UPDATE statement or use a non-existent co, this error will occur.

Now, let’s perform the SELECT statement from this table.

UPDATE OSK_TEST
SET    COLUMN_5 = '123'
WHERE  COLUMN_2 = 'SECOND_VALUE';

Output:

Error report -
SQL Error: ORA-00904: "COLUMN_5": invalid identifier
00904. 00000 -  "%s: invalid identifier"

We used the non-existence column “COLUMN_5” in the above example, so we got this SQL error.

Conclusion

ORA-06512 is a kind of error that commonly occurs while doing CRUD operations in the database like INSERT, UPDATE, DELETE, and QUERY. It might also happen if we tried to use the wrong or non-existent column name in ALTER statement in the database. Here in this article, we have listed the most common causes that cause ORA-00904 errors to occur to help you in the troubleshooting process.