Ora-00904: Invalid Identifier Mistake Inwards Oracle 11G Database - Solved

If y'all conduct keep worked inwards Oracle database ever, y'all would definitely conduct keep seen ORA-00904: invalid identifier error. Doesn't affair which version y'all are working 10g, 11g or 12g, this is 1 of the most mutual error comes land doing CRUD (Create, Read, Update, together with Delete) operations inwards Oracle. By the way, if y'all are beginner, SELECT, INSERT, UPDATE together with DELETE are used to perform CRUD performance inwards Oracle database. What orbit y'all orbit if y'all acquire this error land running inwards SQL script? Like whatever error, y'all should outset pay attending to error message, what is Oracle trying to say here. Invalid identifier way the column advert entered is either missing or invalid, this is 1 of the most mutual stimulate of this error but non the alone one. Some fourth dimension it come upwardly if y'all purpose names, which happened to endure reserved give-and-take inwards Oracle database. Now how orbit y'all resolve it?  We volition larn inwards this article, yesteryear next serial of examples which outset reproduce this error together with afterwards advise how to create it.

In short, hither is the stimulate together with solution of "ORA-00904: invalid identifier error"
Cause : Column advert inwards error is either missing or invalid.
Action : Enter a valid column name. In Oracle database, a valid column advert must laid out amongst a letter, endure less than or equal to xxx characters, together with consist of alone alphanumeric characters together with the exceptional characters $, _, together with #. If it contains other characters, together with hence it must endure enclosed inwards double quotation marks. It may non endure a reserved word.

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

If y'all desire to empathize whatever error, endure it NullPointerException inwards Java or this error inwards Oracle, y'all must outset know how to reproduce it. Until y'all know the existent cause, which y'all would if y'all tin reproduce it regularly, y'all won't endure able to empathize the solution. This is why, I conduct keep listed downward closed to mutual scenarios where I conduct keep seen this error. Here are closed to examples which may Pb to ORA-00904 or "invalid identifier" inwards Oracle 10g database.

Reason 1: Due to extra comma at final column

Yes, an extra comma at the destination of create tabular array disputation tin stimulate "ORA-00904 or "invalid identifier" . This is yesteryear far most mutual argue of this dreaded error and I conduct keep seen developers spent hours to honor out together with fixed this lightheaded mistake. This form of mistakes creeps inwards because of classic re-create together with glue culture. For illustration if y'all are copying column Definition from closed to other table's DDL disputation together with if the said column is non the final 1 y'all volition also re-create comma, together with if y'all position it equally final column inwards your DDL disputation y'all volition run across "ORA-00904: invalid identifier" because after comma Oracle aspect closed to other column declaration. Interesting business office is, your heed volition start focusing on column names of residue of column together with start wondering what's wrong because they all aspect proficient together with and hence most developer volition start doing foreign things, it's hard to run across that final comma inwards a large DDL disputation amongst lots of column together with constraints. For example, hither is how orbit y'all reproduce this error

CREATE TABLE DBA (  ID      NUMBER,  NAME    VARCHAR2(50),  SALARY  NUMBER,    // ' Dont position comma at final column proclamation ' );

If y'all run this inwards SQLFiddle against Oracle 11g database, y'all volition acquire "Schema Creation Failed: ORA-00904: : invalid identifier".

By the way, it's tardily to spot that error inwards elementary tabular array proclamation similar above, how nearly this tabular array 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 ),  toll FLOAT,  quantity NUMBER(10), );

It's slightly hard to spot comma inwards final column declaration, but inwards existent globe tabular array proclamation is much much bigger amongst lots of constraints together with column names. It's amend to explicitly cheque the final column proclamation rather than finding it land running query against database.

Reason ii : Due to Reserved keyword equally Column name

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

If y'all run next query at SQLFiddle (a website where y'all tin attempt SQL query online on whatever database) y'all volition run across the error Schema Creation Failed: ORA-00904: : invalid identifier. The argue our schema creation failed because AUDIT is a reserved give-and-take inwards Oracle 11g R2. Unfortunately SQLFiddle doesn't plow over to a greater extent than details similar SQLDeveloper, Toad or whatever ascendance business tool similar Oracle SQL Plus client e.g. if y'all run the same illustration inwards SQL client, y'all volition run across something similar :

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 honor out culprit inwards this case, equally y'all conduct keep business number together with Oracle is giving y'all plenty hint that AUDIT is invalid identifier. It doesn't tell y'all explicitly that it's a reserved keyword. By the way, y'all don't take away to know all reserved keyword on move yesteryear of your head, y'all tin also ways aspect at next link (http://docs.oracle.com/cd/E11882_01/server.112/e26088/ap_keywd001.htm#SQLRF55621) to run across if that "invalid identifier" error is due to reserved keyword. Some of the keyword which developer oft mistakenly purpose equally column names are COMMENT, CHECK, EXCLUSIVE, INITIAL, LEVEL, ONLINE, PRIOR, RESOURCE, SHARE together with SUCCESSFUL.

ORA-00904: invalid identifier While Inserting information into Table

Apart from tabular array creation, y'all volition run across error "ORA-00904: invalid identifier" if y'all purpose wrong column advert inwards INSERT disputation or purpose a non-existent column name. Most of the fourth dimension it happens because of typo, but closed to other fourth dimension it could endure due to parallel update e.g. mortal changed the schema of tabular array together with renamed or dropped the column y'all are referring inwards INSERT query. hither is an illustration of ORA-00904: invalid identifier land inserting information 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 tin run across that Oracle database complains nearly "DEPT_ID" column equally invalid identifier because at that spot is no such column exists inwards our DBA table.

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

This is the obvious one, if y'all attempt to access an invalid column from a tabular array inwards SELECT query, y'all volition acquire ORA-00904: invalid identifier. For example, if y'all conduct keep next tabular array :

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

together with y'all attempt to execute next SQL SELECT Query :

SQL> SELECT DEPT_ID FROM DBA;

You volition acquire next error "ORA-00904: "DEPT_ID": invalid identifier" because at that spot is no DEPT_ID column inwards DBA table.

ORA-00904: invalid identifier error because or wrong column advert inwards UPDATE query

Just similar previous example, y'all volition acquire this error if y'all purpose wrong or non-existing column advert inwards your UPDATE statement. In next example, nosotros are trying DEPT_ID column which doesn't exists inwards 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 tin run across that error nicely indicate out that DEPT_ID is invalid column.

Reason v : Due to wrong column advert inwards DELETE query

Similarly to previous illustration of SELECT together with UPDATE query, y'all volition also human face upwardly "ORA-00904: invalid identifier" if y'all plow over wrong column advert inwards DELETE statements. It could endure due to typo or because or recent update inwards schema which dropped the column y'all are using inwards 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 tin run across that Oracle gives y'all hint that "DEPT_ID" is invalid identifier because at that spot is no such column inwards DBA table.

How to Avoid Invalid Identifier Error inwards Oracle database

ORA-00904 tin only endure avoided yesteryear using the valid column advert inwards DDL similar CREATE or ALTER statement. Also for DML statements similar SELECT, UPDATE, INSERT together with DELETE, ORA-00904 tin endure avoided yesteryear using right column advert together with doing 4 oculus cheque to grab whatever typo. If y'all are preparing SQL script to run on production database, brand certain y'all seek out these queries on production re-create of database earlier running it straight on alive database. You should also conduct keep procedure to orbit 4 oculus cheque together with review to avoid such errors.

Similarly if y'all are creating a tabular array brand certain y'all purpose a valid column advert inwards your schema. Influenza A virus subtype H5N1 valid column advert inwards Oracle database

• Must laid out amongst a letter.
• Can non endure of to a greater extent than than xxx characters.
• Must endure made upwardly of alphanumeric characters
• May comprise next exceptional characters: $, _, together with #.
• If the column advert uses whatever other characters, it must endure enclosed inwards double quotation marks.
• Can non endure a reserved word.

That's all nearly how to create ORA-00904: invalid identifier error inwards Oracle 11g database. ORA-00904 is a really elementary issue. ORA-00904 may occur when nosotros attempt to create or alter a tabular array amongst invalid column name. It also may occur when nosotros attempt to reference a non existing column inwards a select / insert / update / delete statement. So precisely think the tips together with solution nosotros conduct keep shared here, it volition assist y'all to apace troubleshoot together with create this error.

Further Learning
Oracle Database 12c Fundamentals By Tim Warner
Oracle PL/SQL Fundamentals vol. I & II
The Complete SQL Bootcamp