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

If y'all receive got worked inward Oracle database ever, y'all would definitely receive got seen ORA-00904: invalid identifier error. Doesn't affair which version y'all are working 10g, 11g or 12g, this is i of the most mutual error comes spell doing CRUD (Create, Read, Update, too Delete) operations inward Oracle. By the way, if y'all are beginner, SELECT, INSERT, UPDATE too DELETE are used to perform CRUD functioning inward Oracle database. What exercise y'all exercise if y'all acquire this error spell running inward 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 get upwards entered is either missing or invalid, this is i of the most mutual stimulate of this error but non the alone one. Some fourth dimension it come upwards if y'all utilization names, which happened to hold out reserved discussion inward Oracle database. Now how exercise y'all resolve it?  We volition acquire inward this article, past times next serial of examples which outset reproduce this error too afterward advise how to prepare it.

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

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

If y'all desire to empathize whatever error, hold out it NullPointerException inward Java or this error inward 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 hold out able to empathize the solution. This is why, I receive got listed downward around mutual scenarios where I receive got seen this error. Here are around examples which may Pb to ORA-00904 or "invalid identifier" inward Oracle 10g database.

Reason 1: Due to extra comma at final column

Yes, an extra comma at the halt of exercise tabular array contention tin stimulate "ORA-00904 or "invalid identifier" . This is past times far most mutual argue of this dreaded error and I receive got seen developers spent hours to uncovering out too fixed this lightheaded mistake. This form of mistakes creeps inward because of classic re-create too glue culture. For illustration if y'all are copying column Definition from around other table's DDL contention too if the said column is non the final i y'all volition also re-create comma, too if y'all position it every bit final column inward your DDL contention y'all volition come across "ORA-00904: invalid identifier" because after comma Oracle await around other column declaration. Interesting purpose is, your take away heed volition start focusing on column names of balance of column too start wondering what's wrong because they all facial expression skillful too and therefore most developer volition start doing foreign things, it's hard to come across that final comma inward a large DDL contention amongst lots of column too constraints. For example, hither is how exercise y'all reproduce this error

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

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

By the way, it's tardily to location that error inward uncomplicated tabular array announcement similar above, how well-nigh 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 ),  cost FLOAT,  quantity NUMBER(10), );

It's slightly hard to location comma inward final column declaration, but inward existent globe tabular array announcement is much much bigger amongst lots of constraints too column names. It's meliorate to explicitly depository fiscal establishment lucifer the final column announcement rather than finding it spell running query against database.

Reason ii : Due to Reserved keyword every bit 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 come across the error Schema Creation Failed: ORA-00904: : invalid identifier. The argue our schema creation failed because AUDIT is a reserved discussion inward Oracle 11g R2. Unfortunately SQLFiddle doesn't give to a greater extent than details similar SQLDeveloper, Toad or whatever ascendancy problem tool similar Oracle SQL Plus client e.g. if y'all run the same illustration inward SQL client, y'all volition come 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 uncovering out culprit inward this case, every bit y'all receive got problem number too 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 involve to know all reserved keyword on move past times of your head, y'all tin also ways facial expression at next link (http://docs.oracle.com/cd/E11882_01/server.112/e26088/ap_keywd001.htm#SQLRF55621) to come across if that "invalid identifier" error is due to reserved keyword. Some of the keyword which developer frequently mistakenly utilization every bit column names are COMMENT, CHECK, EXCLUSIVE, INITIAL, LEVEL, ONLINE, PRIOR, RESOURCE, SHARE too SUCCESSFUL.

ORA-00904: invalid identifier While Inserting information into Table

Apart from tabular array creation, y'all volition come across error "ORA-00904: invalid identifier" if y'all utilization wrong column get upwards inward INSERT contention or utilization a non-existent column name. Most of the fourth dimension it happens because of typo, but around other fourth dimension it could hold out due to parallel update e.g. soul changed the schema of tabular array too renamed or dropped the column y'all are referring inward INSERT query. hither is an illustration of ORA-00904: invalid identifier spell 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 come across that Oracle database complains well-nigh "DEPT_ID" column every bit invalid identifier because in that location is no such column exists inward our DBA table.

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

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

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

too 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 in that location is no DEPT_ID column inward DBA table.

ORA-00904: invalid identifier error because or wrong column get upwards inward UPDATE query

Just similar previous example, y'all volition acquire this error if y'all utilization wrong or non-existing column get upwards inward your UPDATE statement. In next example, nosotros are trying DEPT_ID column which doesn't exists inward 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 come across that error nicely signal out that DEPT_ID is invalid column.

Reason v : Due to wrong column get upwards inward DELETE query

Similarly to previous illustration of SELECT too UPDATE query, y'all volition also human face upwards "ORA-00904: invalid identifier" if y'all give wrong column get upwards inward DELETE statements. It could hold out due to typo or because or recent update inward schema which dropped the column y'all are using inward 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 come across that Oracle gives y'all hint that "DEPT_ID" is invalid identifier because in that location is no such column inward DBA table.

How to Avoid Invalid Identifier Error inward Oracle database

ORA-00904 tin exactly hold out avoided past times using the valid column get upwards inward DDL similar CREATE or ALTER statement. Also for DML statements similar SELECT, UPDATE, INSERT too DELETE, ORA-00904 tin hold out avoided past times using right column get upwards too doing iv oculus depository fiscal establishment lucifer to pick out handgrip of whatever typo. If y'all are preparing SQL script to run on production database, brand certain y'all examine these queries on production re-create of database earlier running it direct on alive database. You should also receive got procedure to exercise iv oculus depository fiscal establishment lucifer too review to avoid such errors.

Similarly if y'all are creating a tabular array brand certain y'all utilization a valid column get upwards inward your schema. H5N1 valid column get upwards inward Oracle database

• Must start amongst a letter.
• Can non hold out of to a greater extent than than xxx characters.
• Must hold out made upwards of alphanumeric characters
• May incorporate next exceptional characters: $, _, too #.
• If the column get upwards uses whatever other characters, it must hold out enclosed inward double quotation marks.
• Can non hold out a reserved word.

That's all well-nigh how to prepare ORA-00904: invalid identifier error inward Oracle 11g database. ORA-00904 is a really uncomplicated issue. ORA-00904 may occur when nosotros attempt to exercise or alter a tabular array amongst invalid column name. It also may occur when nosotros attempt to reference a non existing column inward a select / insert / update / delete statement. So exactly shout out upwards the tips too solution nosotros receive got shared here, it volition assist y'all to apace troubleshoot too prepare this error.

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

Share this post