How to resolve ORA-00904: invalid identifier

Last updated on August 8th, 2018 at 05:24 am

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 Sql tutorials to learn about sql

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-00904 error

  1.  It comes when you put extra comma in the oracle 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

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.

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

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

Leave a Reply