solve ORA-00904: invalid identifier in oracle

Resolve ORA-00904: invalid identifier in oracle.

In this tutorial we will try to find out appropriate solution to ORA-00904: invalid identifier in oracle



It will probably take a minute to sort out this issue.

ORA-00904 occurs because of either in oracle -  
  • column name entered is invalid or
  • column name entered is missing.

How to avoid ORA-00904: invalid identifier in oracle
  • You must enter a valid column name.
  • Column name must begin with a letter,
  • Column name must be less than or equal to 30 characters
  • Column name can consist of only alphanumeric characters and the special characters. Allowed special characters
    • $
    • _
    • #
  • Column name must not contain any Oracle reserved keyword.
  • any other characters in Column name (if any) must be enclosed in double quotation marks.



Scenarios where ORA-00904: invalid identifier will be thrown in Oracle>

  1. column name entered is missing will throw ORA-00904: invalid identifier

create table TEST1(ID number(4),  varchar2(22));

Solution > Enter missing column name.
create table TEST1(ID number(4), NAME varchar2(22));


  1. column name beginning from numeric or special character will throw ORA-00904: invalid identifier

create table TEST1(2ID number(4),  varchar2(22));

Solution > Column name must begin with a letter
create table TEST1(ID number(4), NAME varchar2(22));



  1. column name greater than or equal to 30 characters will throw ORA-00904: invalid identifier

create table TEST1(ID_is_of_the_employee_in_the_company_of_oracle_11g number(4),  varchar2(22));

Solution > Column name must contain less than equal to 30 characters.
create table TEST1(ID number(4), NAME varchar2(22));


  1. In column name special character allowed are $, _ and #. Any other special character will throw ORA-00904: invalid identifier



  1. Column name must not contain any Oracle reserved keyword. If any then it will throw ORA-00904: invalid identifier

create table TEST1(FROM number(4),  varchar2(22));


Solution > Column name must not contain any Oracle reserved keyword.
create table TEST1(ID number(4), NAME varchar2(22));


First create table
create table TEST1(ID number(4), NAME varchar2(22));

Then executing all below  commands will throw  ORA-00904: invalid identifier.

  1. select emp_id from test1;

Solution > Use correct column name.
select id from test1;

  1. DELETE from test1 where emp_id =1;

Solution > Use correct column name.
DELETE from test1 where id =1;

  1. INSERT into test1(emp_id) values (1) ;

Solution > Use correct column name.
INSERT into test1(id ) values (1) ;

  1. update test1 set emp_id =1;

Solution > Use correct column name.
update test1 set id =1;



And it will solve ORA-00904: invalid identifier error in oracle

If you still facing any problem, or if it does not solve your problem completely, please comment below.

eEdit
Must read for you :