How to resolve ORA-00904: invalid identifier

ORA-00904: invalid identifier is one of the common error seen.

ORA-00904: invalid identifier

Reference: Oracle documentation

One of the most common cause of this error Invalid identifier means the column name entered is either missing or invalid. It also Some time comes  when you used reserved word in Oracle database.

Check List to resolve the ORA error

  1.  It comes when you put extra comma in the create table statement
CREATE TABLE EXAMPLE

(

TEST_ID      NUMBER,

TEST_NAME    VARCHAR2(200),

TEST_LOCATION VARCHAR2(4000),

);

TEST_LOCATION VARCHAR2(4000),

*

ERROR at line 5:

ORA-00904: invalid identifier

You must check the extra comma’s  to resolve the problem

2)  You are referring the column name which does not exists in select /update/delete/insert query

CREATE TABLE EXAMPLE_TAB

(

TEST_ID      NUMBER,

TEST_NAME    VARCHAR2(200),

TEST_LOCATION VARCHAR2(4000),

);

 

Select  id  ,test_location from example_tab;

select id  ,test_location from example_tab;

*

ERROR at line 1:

ORA-00904: “ID”: invalid identifier

 

The above error came as wrong column name was given

 

update example_tab set id=1000 where test_name = ‘EXAM’;

update example_tab set test_id=1000 where test_name = ‘EXAM’;

*

ERROR at line 1:

ORA-00904: “ID”: invalid identifier

The above error came as wrong column name was given

 

delete example_tab where location = ‘EXAM’;

delete example_tab where location = ‘EXAM’;

*

ERROR at line 1:

ORA-00904: “LOCATION”: invalid identifier

 

The above error came as wrong column name was given

insert into example_tab (TEST_ID, NAME, TEST_LOCATION)

    values(1000, ‘TECH’, ‘EXAM1’);

insert into example_tab (TEST_ID, NAME, TEST_LOCATION)

*

ERROR at line 1:

ORA-00904: “NAME”: invalid identifier

The above error came as wrong column name was given

3) We get this query if use reserved word as the column name.

The following words are reserved by Oracle. That is, they have a special meaning to Oracle and so cannot be redefined. For this reason, you cannot use them to name database objects such as columns, tables, or indexes.

ACCESS ELSE MODIFY START
ADD EXCLUSIVE NOAUDIT SELECT
ALL EXISTS NOCOMPRESS SESSION
ALTER FILE NOT SET
AND FLOAT NOTFOUND SHARE
ANY FOR NOWAIT SIZE
ARRAYLEN FROM NULL SMALLINT
AS GRANT NUMBER SQLBUF
ASC GROUP OF SUCCESSFUL
AUDIT HAVING OFFLINE SYNONYM
BETWEEN IDENTIFIED ON SYSDATE
BY IMMEDIATE ONLINE TABLE
CHAR IN OPTION THEN
CHECK INCREMENT OR TO
CLUSTER INDEX ORDER TRIGGER
COLUMN INITIAL PCTFREE UID
COMMENT INSERT PRIOR UNION
COMPRESS INTEGER PRIVILEGES UNIQUE
CONNECT INTERSECT PUBLIC UPDATE
CREATE INTO RAW USER
CURRENT IS RENAME VALIDATE
DATE LEVEL RESOURCE VALUES
DECIMAL LIKE REVOKE VARCHAR
DEFAULT LOCK ROW VARCHAR2
DELETE LONG ROWID VIEW
DESC MAXEXTENTS ROWLABEL WHENEVER
DISTINCT MINUS ROWNUM WHERE
DROP MODE ROWS WITH

Reference: Oracle reserved keywords

CREATE TABLE EXAMPLE_TAB

(

TEST_ID      NUMBER,

TEST_NAME    VARCHAR2(200),

COMMENT VARCHAR2(1000),

);

 

COMMENT VARCHAR2(1000)

*

ERROR at line 5:

ORA-00904: invalid identifier

See here comment is the reserved word, so we get the error

 

4)  It happens if you use the wrong alias name

select a.test_name from example_tab b;

select a.test_name from example_tab b;

*

ERROR at line 1:

ORA-00904: “A”.”TEST_NAME”: invalid identifier

5)  This error can also happen if you happen to create the table with case-sensitive column by using double quotes

SQL> CREATE TABLE “EXAMPLE_test”(

“Test_Id”  NUMBER,

“Test_Name” varchar2(40),

“Test_Date” DATE

);

TABLE created.

 

SQL> DESC “EXAMPLE_test”(

Name                                      NULL?    TYPE

—————————————– ——– —————————-

Test_Id                                          NUMBER

Test_Name                                  VARCHAR2(40)

Test_Date                                         DATE

 

SQL> select test_id   from “EXAMPLE_test”;

SELECT test_id   FROM “EXAMPLE_test”*

ERROR at line 1:

ORA-00904: “TEST_ID”: invalid identifier

 

We can see that column are stored in case-sensitive manner in the data dictionary

 

SELECT TABLE_NAME, column_name

FROM user_tab_columns

WHERE UPPER(TABLE_NAME) = ‘ EXAMPLE_test ‘;

TABLE_NAME                     COLUMN_NAME

—————————— ——————————

EXAMPLE_test                          Test_Date

EXAMPLE_test                          Test_Name

EXAMPLE_test                           Test_Id

How to avoid the ORA-00904: invalid identifier

ORA-00904 can simply be avoided by using the valid column name in create or alter statement. Also for DML statements like select/update/delete/insert, ORA-00904 can be avoided by making a valid reference to the column name or the alias.

A valid column name must follow following criteria
– The column name must begin with a letter.
– The column name can not be of more than 30 characters.
– The column name must be made up of alphanumeric characters
– The column name may contain following special characters: $, _, and #.
– If the column name uses any other characters, it must be enclosed in double quotation marks.
– The column name can not be a reserved word.

So ORA-00904 error can be avoided by using correct column. Just after writing the query,double check to make sure it is referencing the correct column name or alias

I hope you enjoying reading the post and it solve the doubt about the common error ORA-00904
Related articles

ORA-00911: invalid character Common Issues and Resolution

ORA-03113: end-of-file on communication channel

How to resolve the ORA-00257 error in Oracle database

ORA-27154: post/wait create failed during startup

How to resolve ORA-29913 with external tables

ora-20001 in Gather schema stats on 11g(FND_HISTOGRAM_COLS)

Concurrent Manager:cleanup_node failed due to ORA-01427