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