ORA-00904: invalid identifier Error in Oracle 11g database - Solved

If you have worked in Oracle database ever, you would definitely have seen ORA-00904: invalid identifier error. Doesn't matter which version you are working 10g, 11g or 12g, this is one of the most common error comes while doing CRUD (Create, Read, Update, and Delete) operations in Oracle. By the way, if you are beginner, SELECT, INSERT, UPDATE and DELETE are used to perform CRUD operation in Oracle database. What do you do if you get this error while running in SQL script? Like any error, you should first pay attention to error message, what is Oracle trying to say here. Invalid identifier means the column name entered is either missing or invalid, this is one of the most common cause of this error but not the only one. Some time it come if you use names, which happened to be reserved word in Oracle database. Now how do you resolve it?  We will learn in this article, by following series of examples which first reproduce this error and later suggest how to fix it.





In short, here is the cause and solution of "ORA-00904: invalid identifier error"

Cause : Column name in error is either missing or invalid.

Action : Enter a valid column name. In Oracle database, a valid column name must begin with a letter, be less than or equal to 30 characters, and consist of only alphanumeric characters and the special characters $, _, and #. If it contains other characters, then it must be enclosed in double quotation marks. It may not be a reserved word.

Some reasons of "ORA-00904: invalid identifier error"

If you want to understand any error, be it NullPointerException in Java or this error in Oracle, you must first know how to reproduce it. Until you know the real cause, which you would if you can reproduce it regularly, you won't be able to understand the solution. This is why, I have listed down some common scenarios where I have seen this error. Here are some examples which may lead to ORA-00904 or "invalid identifier" in Oracle 10g database.

Reason 1: Due to extra comma at last column

Yes, an extra comma at the end of create table statement can cause "ORA-00904 or "invalid identifier" . This is by far most common reason of this dreaded error and I have seen developers spent hours to find out and fixed this silly mistake. This kind of mistakes creeps in because of classic copy and paste culture. For example if you are copying column definition from some other table's DDL statement and if the said column is not the last one you will also copy comma, and if you put it as last column in your DDL statement you will see "ORA-00904: invalid identifier" because after comma Oracle expect another column declaration. Interesting part is, your mind will start focusing on column names of rest of column and start wondering what's wrong because they all look good and then most developer will start doing strange things, it's hard to see that last comma in a big DDL statement with lots of column and constraints. For example, here is how do you reproduce this error

CREATE TABLE DBA
(
 ID      NUMBER,
 NAME    VARCHAR2(50),
 SALARY  NUMBER,    // ' Dont put comma at last column declaration '
);

If you run this in SQLFiddle against Oracle 11g database, you will get "Schema Creation Failed: ORA-00904: : invalid identifier".



By the way, it's easy to spot that error in simple table declaration like above, how about this table declaration

CREATE TABLE Items
(
 itemId NUMBER(10),
 CONSTRAINT primary_pk PRIMARY KEY (itemId),
 itemname VARCHAR2(100),
 catogoryId NUMBER(10),
 CONSTRAINT subcategory_fk FOREIGN KEY (catogoryId ) REFERENCES itemSubCategory(catogoryId ),
 companyId VARCHAR2(20),
 CONSTRAINT company_fk FOREIGN KEY(companyId ) REFERENCES CompanyInfo(companyId ),
 description VARCHAR2(1000),
 supplierId VARCHAR2(20),
 CONSTRAINT supplier_fk FOREIGN KEY(supplierId ) REFERENCES SupplierInfo(supplierId ),
 price FLOAT,
 quantity NUMBER(10),
);

It's slightly difficult to spot comma in last column declaration, but in real world table declaration is much much bigger with lots of constraints and column names. It's better to explicitly check the last column declaration rather than finding it while running query against database.

Reason 2 : Due to Reserved keyword as Column name

CREATE TABLE DBA
(
 ID      NUMBER,
 NAME    VARCHAR2(50),
 AUDIT   VARCHAR2(1000)
);

If you run following query at SQLFiddle (a website where you can try SQL query online on any database) you will see the error Schema Creation Failed: ORA-00904: : invalid identifier. The reason our schema creation failed because AUDIT is a reserved word in Oracle 11g R2. Unfortunately SQLFiddle doesn't give more details like SQLDeveloper, Toad or any command line tool like Oracle SQL Plus client e.g. if you run the same example in SQL client, you will see something like :

SQL> CREATE TABLE DBA
  2  (
  3     ID      NUMBER,
  4     NAME    VARCHAR2(50),
  5     AUDIT VARCHAR2(1000)
  6  );

 AUDIT VARCHAR2(1000)
 *
ERROR at line 5:
ORA-00904: invalid identifier

It's much easier to find out culprit in this case, as you have line number and Oracle is giving you enough hint that AUDIT is invalid identifier. It doesn't tell you explicitly that it's a reserved keyword. By the way, you don't need to know all reserved keyword on top of your head, you can also ways look at following link (http://docs.oracle.com/cd/E11882_01/server.112/e26088/ap_keywd001.htm#SQLRF55621) to see if that "invalid identifier" error is due to reserved keyword. Some of the keyword which developer often mistakenly use as column names are COMMENT, CHECK, EXCLUSIVE, INITIAL, LEVEL, ONLINE, PRIOR, RESOURCE, SHARE and SUCCESSFUL.

ORA-00904: invalid identifier While Inserting data into Table

Apart from table creation, you will see error "ORA-00904: invalid identifier" if you use wrong column name in INSERT statement or use a non-existent column name. Most of the time it happens because of typo, but some other time it could be due to parallel update e.g. someone changed the schema of table and renamed or dropped the column you are referring in INSERT query. here is an example of ORA-00904: invalid identifier while inserting data into table

SQL> insert into DBA values (102, 'Mohan', 10500); //Ok

SQL> insert into DBA(ID, NAME, SALARY) values (101, 'John',  10000); //Ok

SQL> insert into DBA(ID, NAME, SALARY, DEPT_ID) values (101, 'John',  10000, 1); // Not Ok
ORA-00904: "DEPT_ID": invalid identifier : insert into DBA(ID, NAME, SALARY, DEPT_ID) values (101, 'John', 10000, 1)

You can see that Oracle database complains about "DEPT_ID" column as invalid identifier because there is no such column exists in our DBA table.

ORA-00904: invalid identifier due to accessing non-existing column in SELECT

This is the obvious one, if you try to access an invalid column from a table in SELECT query, you will get ORA-00904: invalid identifier. For example, if you have following table :

CREATE TABLE DBA
(
 ID      NUMBER,
 NAME    VARCHAR2(50),
 SALARY  NUMBER
);

and you try to execute following SQL SELECT Query :

SQL> SELECT DEPT_ID FROM DBA;

You will get following error "ORA-00904: "DEPT_ID": invalid identifier" because there is no DEPT_ID column in DBA table.

ORA-00904: invalid identifier error because or wrong column name in UPDATE query

Just like previous example, you will get this error if you use wrong or non-existing column name in your UPDATE statement. In following example, we are trying DEPT_ID column which doesn't exists in DBA table, that's why ORA-00904: invalid identifier error

SQL> UPDATE DBA set DEPT_ID=1 where ID=101;
ORA-00904: "DEPT_ID": invalid identifier : UPDATE DBA set DEPT_ID=1 where ID=101

You can see that error nicely point out that DEPT_ID is invalid column.

Reason 5 : Due to incorrect column name in DELETE query

Similarly to previous example of SELECT and UPDATE query, you will also face "ORA-00904: invalid identifier" if you give wrong column name in DELETE statements. It could be due to typo or because or recent update in schema which dropped the column you are using in your DELETE clause.

SQL> DELETE FROM DBA WHERE ID=101;  // Ok

SQL> DELETE FROM DBA WHERE DEPT_ID=1;  // Not Ok, ORA-00904: invalid identifier
ORA-00904: "DEPT_ID": invalid identifier : delete from DBA where DEPT_ID=1

You can see that Oracle gives you hint that "DEPT_ID" is invalid identifier because there is no such column in DBA table.

How to Avoid Invalid Identifier Error in Oracle database

ORA-00904 can simply be avoided by using the valid column name in DDL like CREATE or ALTER statement. Also for DML statements like SELECT, UPDATE, INSERT and DELETE, ORA-00904 can be avoided by using correct column name and doing four eye check to catch any typo. If you are preparing SQL script to run on production database, make sure you test these queries on production copy of database before running it directly on live database. You should also have process to do four eye check and review to avoid such errors.



Similarly if you are creating a table make sure you use a valid column name in your schema. A valid column name in Oracle database

• Must begin with a letter.


• Can not be of more than 30 characters.


• Must be made up of alphanumeric characters


• May contain following special characters: $, _, and #.


• If the column name uses any other characters, it must be enclosed in double quotation marks.


• Can not be a reserved word.

That's all about how to fix ORA-00904: invalid identifier error in Oracle 11g database. ORA-00904 is a very simple issue. ORA-00904 may occur when we try to create or alter a table with invalid column name. It also may occur when we try to reference a non existing column in a select / insert / update / delete statement. So just remember the tips and solution we have shared here, it will help you to quickly troubleshoot and fix this error.

Source:http://javarevisited.blogspot.com/2014/09/ora-00904-invalid-identifier-error-in-11g-database.html