Home » Oracle » Oracle Database » ORA-00904: invalid identifier in Oracle

ORA-00904: invalid identifier in Oracle

ORA-00904 : invalid identifier is one of the common error seen. You might get creating tables, selecting from tables and many more. It most happens because of the syntax issue. We have to be very careful with the syntax. The key is to remember syntax and write the code  correctly. You might want to check the Oracle Sql tutorials to learn about SQL

ORA-00904: invalid identifier

Reference: Oracle documentation

One of the most common cause of this error ORA-00904: 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-00904: invalid identifier

(1) It comes when you put extra comma in the create table in oracle 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
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

ora-00904 invalid identifier

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

See also  AD Utilities(adadmin, adrelink , adident, ADMRGPCH)

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, oracle tables, or oracle indexes.

ACCESSELSEMODIFYSTART
ADDEXCLUSIVENOAUDITSELECT
ALLEXISTSNOCOMPRESSSESSION
ALTERFILENOTSET
ANDFLOATNOTFOUNDSHARE
ANYFORNOWAITSIZE
ARRAYLENFROMNULLSMALLINT
ASGRANTNUMBERSQLBUF
ASCGROUPOFSUCCESSFUL
AUDITHAVINGOFFLINESYNONYM
BETWEENIDENTIFIEDONSYSDATE
BYIMMEDIATEONLINETABLE
CHARINOPTIONTHEN
CHECKINCREMENTORTO
CLUSTERINDEXORDERTRIGGER
COLUMNINITIALPCTFREEUID
COMMENTINSERTPRIORUNION
COMPRESSINTEGERPRIVILEGESUNIQUE
CONNECTINTERSECTPUBLICUPDATE
CREATEINTORAWUSER
CURRENTISRENAMEVALIDATE
DATELEVELRESOURCEVALUES
DECIMALLIKEREVOKEVARCHAR
DEFAULTLOCKROWVARCHAR2
DELETELONGROWIDVIEW
DESCMAXEXTENTSROWLABELWHENEVER
DISTINCTMINUSROWNUMWHERE
DROPMODEROWSWITH

Reference: Oracle reserved keywords

CREATE TABLE EXAMPLE1_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

ORA-00904: invalid identifier
Note: we can use reserved keyword by enclosing in inverted commas

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

See also  Top queries to find Patch applied in Oracle apps

A valid column name must follow following criteria
– it must begin with a letter.
– The name can not be of more than 30 characters.
– Column name must be made up of alphanumeric characters
– it 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 enjoy reading the post and it solved the doubt about the common error ORA-00904 we see while working with Oracle

Do let me know where other areas you get this error and what you did to resolve it.

Related articles
ORA-00911: invalid character : This post is for common causes of ORA-00911: invalid character in oracle with examples and resolution to help you complete the job
ORA-03113: end-of-file on communication channel : Check out method to resolve ORA-03113: end-of-file on communication channel. This error could happen in database startup, running program
ORA-00257 :Learn how to troubleshoot for ORA-00257 archiver error. Connect internal only error.Various resolution and example provided in step by step manner.
ORA-27154: post/wait create failed during startup : Troubleshooting Ora Errors ORA-27154: post/wait create failed / ORA-27146: post/wait initialization failed while starting the oracle database
ORA-29913 with external tables : troubleshooting tips for the ORA errors like ora-29913: error in executing odciexttableopen callout, ora-29913: error in executing odciexttablefetch callout
ora-20001 in Gather schema stats on 11g(FND_HISTOGRAM_COLS)
ORA-01427 :Check out this for the solution on ORA-01427: single-row subquery returns more than one row error ,how to resolve it when it happens with Concurrent Manager

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top