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));

2. 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));

3. 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));

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

5. 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.

6. select emp_id from test1;

Solution > Use correct column name.

select id from test1;

7. DELETE from test1 where emp_id =1;

Solution > Use correct column name.

DELETE from test1 where id =1;

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

Solution > Use correct column name.

INSERT into test1(id ) values (1) ;

9. 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.

Labels: Oracle Oracle ORA errors