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: 10.2.0.1 to 11.2.0.1 - Release: 10.2 to 11.2
Information in this document applies to any platform.

Symptoms

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)
SELECT
trunc(trunc(sysdate-1-6/24),'HH24') + 6/24 time
FROM
db_link_test@dblink
ORDER BY
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.

Cause

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 hundreds of Community platforms