ORA-00904 invalid identifier Solution

Did you get an “ORA-00904: invalid identifier” error? Find out what caused the ORA-00904 error and how to resolve it in this article.

Table of Contents

ORA-00904 Cause

So, you ran an SQL statement and you got this error:

ORA-00904: invalid identifier

What causes this error?

It’s caused by referring to a column that does not exist, or you are referring to an invalid alias in a SELECT statement.

It could also mean you’re using quotes when they aren’t needed. We can check this as part of the solution below.

ORA-00904 Solution

To resolve the ORA-00904 error, check the following:

Check that your column name exists in the table you’re referring to
Check that you’re referring to the correct alias when referring to the column
Check that you’re using the correct table alias for the column (e.g. if the department table is aliased as dept, then it should be dept.department_id and not sup.department_id)
Check that you’re not referring to a column alias inside a WHERE clause
Check that the column name is 30 characters or less
Check that the column name contains only alphanumeric characters, $, _, and #, or it is contained in double quotes.
Check that it is not a reserved word. Many IDEs will highlight the word if it is reserved.

Let’s see an example.

This query will return an ORA-00904 error:

SELECT student_id AS stid, first_name, last_name
FROM student
WHERE st_id = 10;

ERROR at line 3:
ORA-00904: "ST_ID": invalid identifier

This error occurred on line 3, according to the output. The line numbers for errors can be helpful in working out the cause.

The error happened because I gave an alias to the student_id column, which is “stid”. But, in the WHERE clause, I called it “st_id”. There is no column or alias called st_id, so the query displayed an error.

To correct it, I should refer to the alias correctly. If it’s a WHERE clause, I need to use the column name. If it’s an ORDER BY, I can use the column name or the alias.

This is what happens when I try to use an alias inside a WHERE clause:

SELECT student_id AS stid, first_name, last_name
FROM student
WHERE stid = 10;

ERROR at line 3:
ORA-00904: "STID": invalid identifier

This is because the WHERE clause doesn’t know about column aliases. I need to refer to the foll column name.

SELECT student_id AS stid, first_name, last_name
FROM student
WHERE student_id = 10;

This query runs without issues.

While you’re here, if you want an easy-to-use list of the main features in Oracle SQL, get my SQL Cheat Sheet here:

ORA-00904 Invalid Identifier But Column Exists

Are you getting this error but you’re sure the column exists?

Take a look at the steps in the solution section above.

Make sure you’re not referring to the column alias in a WHERE clause, that you have double quotes if it contains special characters, that you’re referring to the right table, and that it’s not a reserved word.

ORA-00904 Invalid Identifier Insert Statement

Are you getting an ORA-00904 error when using an INSERT statement?

If so, there’s a reason why. It’s to do with double quotes in Oracle.

Let’s say I had a books table that I created like this.

CREATE TABLE books (
  book_id NUMBER(5),
  title VARCHAR2(100),
  "author" VARCHAR2(100)
);

Note the double quotes used when specifying “author”.

Now, when I insert into the table:

INSERT INTO books (book_id, title, author)
VALUES (1, 'The Adventure', 'John Smith');

SQL Error: ORA-00904: "AUTHOR": invalid identifier

Why is this causing an issue? There is clearly an author column in the table and in the statement, they are both spelt the same.

The error occurs because using double quotes in Oracle CREATE TABLE statements means the correct case, or double quotes, always need to be used.

Oracle has stored the column name in the table as “author”, all in lower case, because it is in quotes. When this happens, statements looking for this column are case-sensitive.

When I write the INSERT query, I specified author, without quotes. Oracle converts this to upper case, and because this match is case-sensitive, the column isn’t found. This is because AUTHOR is different to author.

To fix this, you can do one of two things:

Change your DDL when you create your table to remove the quotes.
If you can’t do this, then all of your SQL statements need to refer to this column using quotes. Update the INSERT statement to match.

A statement may look like this:

INSERT INTO books (book_id, title, "author")
VALUES (1, 'The Adventure', 'John Smith');

This row has been inserted successfully.

ORA-00904 Invalid Identifier Create Table

You can get this “invalid identifier” error when you create a table.

Consider this example:

CREATE TABLE books (
  CONSTRAINT book_id NUMBER(5) PRIMARY KEY,
  title VARCHAR2(100)
);

I’ve tried to create a table called books, with a book_id and a title. However, I get the follow error when I run this statement:

Error starting at line : 1 in command -
CREATE TABLE books
(
CONSTRAINT book_id NUMBER(5) PRIMARY KEY,
title VARCHAR2(100)
)
Error report -
SQL Error: ORA-00904: : invalid identifier
00904. 00000 -  "%s: invalid identifier"
*Cause:
*Action:

It doesn’t really tell me much, other than a “%s”, which doesn’t help me.

The reason this error has occurred is because a constraint refers to a column that does not exist.

In the first line inside the brackets, I specify CONSTRAINT, then the book_id column, data type and primary key.

However, this syntax means I am declaring a constraint. But, I have not yet declared the column yet.

One of the ways to correct this table definition is to remove the CONSTRAINT keyword.

CREATE TABLE books (
  book_id NUMBER(5) PRIMARY KEY,
  title VARCHAR2(100)
);

An even better way is to separate the column definition and the constraint creation. This is better because you can name the constraint, which makes it easier to manage later.

CREATE TABLE books (
  book_id NUMBER(5),
  title VARCHAR2(100),
  CONSTRAINT pk_books PRIMARY KEY (book_id)
);

For more information on creating tables, read my guide on the CREATE TABLE statement.

So, that’s how you can resolve the ORA-00904 error.