Search notes:

ORA-00904: invalid identifier

ORA-00940: invalid identifier typically indicates that the SQL parser recognized a token as an identifier but the identifier referred to nothing.

For exampile, dual has no column named no_such_column, therefore, the following SQL statement throws this error:

select
   dummy,
   no_such_column
from
   dual;

This error is often seen when a user thinks a function exists which in reality does not. For example, a developer coming from SQL Server might think Oracle has a function named isNull and hit such an error:

select
   isnull(dummy, 'Y') dummy_
   from
     dual;

The error message ORA-00904: invalid identifier might also be caused in a create table statement if a column name was expected but none was given:

create table tq84_invalid_identifier (
   id    number(9)    primary key,
   val   varchar2(10)            ,  -- <== Note the comma
);

UNION ALL in combination with ORDER BY

Interestingly, an ORA-00904 is also thrown in the following statement (even though dual has a column named dummy):

select * from dual union all
select * from dual
order by dummy;

If the order by clause is applied to the first query, Oracle throws ORA-00933: SQL command not preperly ended.

Fatal error: Uncaught PDOException: SQLSTATE[HY000]: General error: 8 attempt to write a readonly database in /home/httpd/vhosts/renenyffenegger.ch/php/web-request-database.php:78 Stack trace: #0 /home/httpd/vhosts/renenyffenegger.ch/php/web-request-database.php(78): PDOStatement->execute(Array) #1 /home/httpd/vhosts/renenyffenegger.ch/php/web-request-database.php(30): insert_webrequest_('/notes/developm...', 1759406252, '216.73.216.42', 'Mozilla/5.0 App...', NULL) #2 /home/httpd/vhosts/renenyffenegger.ch/httpsdocs/notes/development/databases/Oracle/errors/ORA-00904_invalid-identifier/index(75): insert_webrequest() #3 {main} thrown in /home/httpd/vhosts/renenyffenegger.ch/php/web-request-database.php on line 78

ORA-00904: invalid identifier

UNION ALL in combination with ORDER BY

See also