My Oracle Support Banner

ORA-00904: "<column_name>": Invalid Identifier Error on Insert As Remote Select With Order By (Doc ID 1172793.1)

Last updated on AUGUST 10, 2010

Applies to:

Oracle Server - Enterprise Edition - Version: to - Release: 10.2 to 11.2
Information in this document applies to any platform.


An error ORA-00904 'invalid identifier' is received if an insert as select from a remote table is executed, and the select contains an order by clause in which the order by column that does not appear in the select list of the select statement.

For example:
insert into db_link_test2(col1)
trunc(trunc(sysdate-1-6/24),'HH24') + 6/24 time
datum desc;
datum desc
ERROR at line 7:
ORA-904: "DATUM": invalid identifier

Note that DATUM column does not appear in the list of columns of the select clause.

This works fine if the ORDER BY clause is removed.  It also produces no error if the source site is version 9.2.


To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.