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

If yous receive got worked inward Oracle database ever, yous would definitely receive got seen ORA-00904: invalid identifier error. Doesn't thing which version yous are working 10g, 11g or 12g, this is i of the most mutual error comes spell doing CRUD (Create, Read, Update, together with Delete) operations inward Oracle. By the way, if yous are beginner, SELECT, INSERT, UPDATE together with DELETE are used to perform CRUD performance inward Oracle database. What do yous do if yous acquire this error spell running inward SQL script? Like whatsoever error, yous should starting fourth dimension pay attending to error message, what is Oracle trying to say here. Invalid identifier agency the column lift entered is either missing or invalid, this is i of the most mutual get of this error but non the entirely one. Some fourth dimension it come upwards if yous job names, which happened to live on reserved discussion inward Oracle database. Now how do yous resolve it?  We volition larn inward this article, past times next serial of examples which starting fourth dimension reproduce this error together with afterward advise how to cook it.

In short, hither is the gather with solution of "ORA-00904: invalid identifier error"
Cause : Column lift inward error is either missing or invalid.
Action : Enter a valid column name. In Oracle database, a valid column lift must start alongside a letter, live on less than or equal to thirty characters, together with consist of entirely alphanumeric characters together with the exceptional characters $, _, together with #. If it contains other characters, together with thence it must live on enclosed inward double quotation marks. It may non live on a reserved word.

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

If yous desire to empathize whatsoever error, live on it NullPointerException inward Java or this error inward Oracle, yous must starting fourth dimension know how to reproduce it. Until yous know the existent cause, which yous would if yous tin reproduce it regularly, yous won't live on able to empathize the solution. This is why, I receive got listed downward approximately mutual scenarios where I receive got seen this error. Here are approximately examples which may atomic number 82 to ORA-00904 or "invalid identifier" inward Oracle 10g database.

Reason 1: Due to extra comma at concluding column

Yes, an extra comma at the cease of do tabular array disceptation tin get "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 notice out together with fixed this dizzy mistake. This variety of mistakes creeps inward because of classic re-create together with glue culture. For representative if yous are copying column Definition from approximately other table's DDL disceptation together with if the said column is non the concluding i yous volition also re-create comma, together with if yous pose it every bit concluding column inward your DDL disceptation yous volition meet "ORA-00904: invalid identifier" because after comma Oracle facial expression approximately other column declaration. Interesting job is, your hear volition start focusing on column names of residual of column together with start wondering what's wrong because they all facial expression practiced together with and thence most developer volition start doing foreign things, it's hard to meet that concluding comma inward a large DDL disceptation alongside lots of column together with constraints. For example, hither is how do yous reproduce this error

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

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

By the way, it's slow to location that error inward uncomplicated tabular array announcement 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 inward concluding column declaration, but inward existent earth tabular array announcement is much much bigger alongside lots of constraints together with column names. It's amend to explicitly depository fiscal establishment fit the concluding 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 yous run next query at SQLFiddle (a website where yous tin effort SQL query online on whatsoever database) yous volition meet 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 plow over to a greater extent than details similar SQLDeveloper, Toad or whatsoever ascendance trace of piece of work tool similar Oracle SQL Plus client e.g. if yous run the same representative inward SQL client, yous volition meet 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 inward this case, every bit yous receive got trace of piece of work number together with Oracle is giving yous plenty hint that AUDIT is invalid identifier. It doesn't tell yous explicitly that it's a reserved keyword. By the way, yous don't ask to know all reserved keyword on move past times of your head, yous tin also ways facial expression at next link (http://docs.oracle.com/cd/E11882_01/server.112/e26088/ap_keywd001.htm#SQLRF55621) to meet if that "invalid identifier" error is due to reserved keyword. Some of the keyword which developer ofttimes mistakenly job every bit 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, yous volition meet error "ORA-00904: invalid identifier" if yous job wrong column lift inward INSERT disceptation or job a non-existent column name. Most of the fourth dimension it happens because of typo, but approximately other fourth dimension it could live on due to parallel update e.g. individual changed the schema of tabular array together with renamed or dropped the column yous are referring inward INSERT query. hither is an representative 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 meet that Oracle database complains nearly "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 yous effort to access an invalid column from a tabular array inward SELECT query, yous volition acquire ORA-00904: invalid identifier. For example, if yous receive got next tabular array :

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

together with yous effort 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 lift inward UPDATE query

Just similar previous example, yous volition acquire this error if yous job wrong or non-existing column lift 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 meet that error nicely betoken out that DEPT_ID is invalid column.

Reason v : Due to wrong column lift inward DELETE query

Similarly to previous representative of SELECT together with UPDATE query, yous volition also human face upwards "ORA-00904: invalid identifier" if yous plow over wrong column lift inward DELETE statements. It could live on due to typo or because or recent update inward schema which dropped the column yous 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 meet that Oracle gives yous 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 merely live on avoided past times using the valid column lift inward DDL similar CREATE or ALTER statement. Also for DML statements similar SELECT, UPDATE, INSERT together with DELETE, ORA-00904 tin live on avoided past times using right column lift together with doing 4 optic depository fiscal establishment fit to grab whatsoever typo. If yous are preparing SQL script to run on production database, brand certain yous examine these queries on production re-create of database earlier running it direct on alive database. You should also receive got procedure to do 4 optic depository fiscal establishment fit together with review to avoid such errors.

Similarly if yous are creating a tabular array brand certain yous job a valid column lift inward your schema. Influenza A virus subtype H5N1 valid column lift inward Oracle database

• Must start alongside a letter.
• Can non live on of to a greater extent than than thirty characters.
• Must live on made upwards of alphanumeric characters
• May incorporate next exceptional characters: $, _, together with #.
• If the column lift uses whatsoever other characters, it must live on enclosed inward double quotation marks.
• Can non live on a reserved word.

That's all nearly how to cook ORA-00904: invalid identifier error inward Oracle 11g database. ORA-00904 is a really uncomplicated issue. ORA-00904 may occur when nosotros effort to do or alter a tabular array alongside invalid column name. It also may occur when nosotros effort to reference a non existing column inward a select / insert / update / delete statement. So precisely hollo back the tips together with solution nosotros receive got shared here, it volition aid yous to speedily troubleshoot together with cook this error.

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

Bagikan Artikel ini