Why Am I Getting ORA-00904: 'invalid identifier' When Querying My Oracle Table?

ORA-00904 Error: Invalid Identifier

Problem:

When attempting to retrieve data from a table using a simple query where the WHERE clause includes a condition on a column value, you encounter the ORA-00904 error indicating an invalid identifier.

For instance, you have a table named reg1 created with the following columns:

create table reg1 (
  fname varchar2(30),
  lname varchar2(30),
  addr varchar2(30),
  mail varchar2(30),
  occu varchar2(30),
  uname varchar2(30),
  passwd varchar2(30)
);

However, when executing a query to fetch the first name (fname) and last name (lname) of a user with the username 'bbb' like:

select fname, lname
  from reg1
 where uname="bbb";

You receive the error ORA-00904: "bbb": invalid identifier.

Answer:

The error occurs because the value you are comparing against in the WHERE clause is not enclosed in single quotes. In Oracle, when comparing string values, you need to quote them with single quotes to indicate that they are literals.

To resolve the issue, simply add single quotes around the 'bbb' value:

select fname, lname
  from reg1
 where uname='bbb';

With this correction, the query will successfully fetch the data for the user with the username 'bbb', as the comparison is now treated correctly as a literal string comparison.

The above is the detailed content of Why Am I Getting ORA-00904: 'invalid identifier' When Querying My Oracle Table?. For more information, please follow other related articles on the PHP Chinese website!