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

Baca Juga

If you lot lead keep worked inwards Oracle database ever, you lot would definitely lead keep seen ORA-00904: invalid identifier error. Doesn't affair which version you lot are working 10g, 11g or 12g, this is 1 of the most mutual error comes piece doing CRUD (Create, Read, Update, as well as Delete) operations inwards Oracle. By the way, if you lot are beginner, SELECT, INSERT, UPDATE as well as DELETE are used to perform CRUD functioning inwards Oracle database. What hit you lot hit if you lot acquire this error piece running inwards SQL script? Like whatever error, you lot should outset pay attending to error message, what is Oracle trying to say here. Invalid identifier agency the column advert entered is either missing or invalid, this is 1 of the most mutual induce of this error but non the alone one. Some fourth dimension it come upwards if you lot role names, which happened to endure reserved discussion inwards Oracle database. Now how hit you lot resolve it?  We volition acquire inwards this article, past times next serial of examples which outset reproduce this error as well as afterwards propose how to create it.

In short, hither is the induce as well as 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 about alongside a letter, endure less than or equal to thirty characters, as well as consist of alone alphanumeric characters as well as the exceptional characters $, _, as well as #. If it contains other characters, as well as then it must endure enclosed inwards double quotation marks. It may non endure a reserved word.

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

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

Reason 1: Due to extra comma at terminal column

Yes, an extra comma at the terminate of create tabular array disceptation tin induce "ORA-00904 or "invalid identifier" . This is past times far most mutual argue of this dreaded error and I lead keep seen developers spent hours to notice out as well as fixed this featherbrained mistake. This sort of mistakes creeps inwards because of classic re-create as well as glue culture. For instance if you lot are copying column Definition from unopen to other table's DDL disceptation as well as if the said column is non the terminal 1 you lot volition also re-create comma, as well as if you lot set it equally terminal column inwards your DDL disceptation you lot volition run across "ORA-00904: invalid identifier" because after comma Oracle await unopen to other column declaration. Interesting business office is, your take away heed volition start focusing on column names of residue of column as well as start wondering what's wrong because they all human face practiced as well as and then most developer volition start doing foreign things, it's hard to run across that terminal comma inwards a large DDL disceptation alongside lots of column as well as constraints. For example, hither is how hit you lot reproduce this error

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

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

By the way, it's slowly to location 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 ),  cost FLOAT,  quantity NUMBER(10), );

It's slightly hard to location comma inwards terminal column declaration, but inwards existent footing tabular array proclamation is much much bigger alongside lots of constraints as well as column names. It's ameliorate to explicitly depository fiscal establishment agree the terminal column proclamation rather than finding it piece running query against database.

Reason two : Due to Reserved keyword equally Column name

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

If you lot run next query at SQLFiddle (a website where you lot tin endeavour SQL query online on whatever database) you lot volition run across the error Schema Creation Failed: ORA-00904: : invalid identifier. The argue our schema creation failed because AUDIT is a reserved discussion inwards Oracle 11g R2. Unfortunately SQLFiddle doesn't give to a greater extent than details similar SQLDeveloper, Toad or whatever ascendence describe of piece of occupation tool similar Oracle SQL Plus client e.g. if you lot run the same instance inwards SQL client, you lot 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 notice out culprit inwards this case, equally you lot lead keep describe of piece of occupation number as well as Oracle is giving you lot plenty hint that AUDIT is invalid identifier. It doesn't tell you lot explicitly that it's a reserved keyword. By the way, you lot don't take away to know all reserved keyword on laissez passer on of your head, you lot tin also ways human face 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 oftentimes mistakenly role equally column names are COMMENT, CHECK, EXCLUSIVE, INITIAL, LEVEL, ONLINE, PRIOR, RESOURCE, SHARE as well as SUCCESSFUL.

ORA-00904: invalid identifier While Inserting information into Table

Apart from tabular array creation, you lot volition run across error "ORA-00904: invalid identifier" if you lot role wrong column advert inwards INSERT disceptation or role a non-existent column name. Most of the fourth dimension it happens because of typo, but unopen to other fourth dimension it could endure due to parallel update e.g. mortal changed the schema of tabular array as well as renamed or dropped the column you lot are referring inwards INSERT query. hither is an instance of ORA-00904: invalid identifier piece 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 in that location 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 you lot endeavour to access an invalid column from a tabular array inwards SELECT query, you lot volition acquire ORA-00904: invalid identifier. For example, if you lot lead keep next tabular array :

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

as well as you lot endeavour 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 inwards DBA table.

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

Just similar previous example, you lot volition acquire this error if you lot role 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 instance of SELECT as well as UPDATE query, you lot volition also confront "ORA-00904: invalid identifier" if you lot give wrong column advert inwards DELETE statements. It could endure due to typo or because or recent update inwards schema which dropped the column you lot 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 you lot hint that "DEPT_ID" is invalid identifier because in that location is no such column inwards DBA table.

How to Avoid Invalid Identifier Error inwards Oracle database

ORA-00904 tin only endure avoided past times using the valid column advert inwards DDL similar CREATE or ALTER statement. Also for DML statements similar SELECT, UPDATE, INSERT as well as DELETE, ORA-00904 tin endure avoided past times using right column advert as well as doing iv oculus depository fiscal establishment agree to grab whatever typo. If you lot are preparing SQL script to run on production database, brand certain you lot evidence these queries on production re-create of database earlier running it direct on alive database. You should also lead keep procedure to hit iv oculus depository fiscal establishment agree as well as review to avoid such errors.

Similarly if you lot are creating a tabular array brand certain you lot role a valid column advert inwards your schema. Influenza A virus subtype H5N1 valid column advert inwards Oracle database

• Must laid about alongside a letter.
• Can non endure of to a greater extent than than thirty characters.
• Must endure made upwards of alphanumeric characters
• May comprise next exceptional characters: $, _, as well as #.
• 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 real elementary issue. ORA-00904 may occur when nosotros endeavour to create or alter a tabular array alongside invalid column name. It also may occur when nosotros endeavour to reference a non existing column inwards a select / insert / update / delete statement. So exactly squall upwards the tips as well as solution nosotros lead keep shared here, it volition assist you lot to apace troubleshoot as well as create this error.

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