Outer Join Query Over Dblink Can Fail With ORA-904 (Doc ID 730256.1)

Last updated on SEPTEMBER 04, 2015

Applies to:

Oracle Database - Enterprise Edition - Version 10.2.0.1 to 11.1.0.7 [Release 10.2 to 11.1]
Information in this document applies to any platform.

Symptoms


Following query over dblink having Local database 9iR2 and remote 10gR2 works..

select *
from ctps3.institutions@ctps3 i,
ctps3.state_types@ctps3 s,ods.institutions oi
where s.stt_state_type_id (+) = i.stt_state_type_id
and oi.ctps_insttn_id = i.inst_insttn_id
and i.inst_modifd_dt = trunc(sysdate)
and (nvl (oi.inst_state_txt, 'x') <> nvl (s.stt_state_type_nm, 'x'))
and (nvl (oi.inst_phone_txt, 17) <> nvl (i.inst_phone_txt, 17));

The same query fails when both local and remote databases are 10g R2, with following errors:

ERROR at line 2:
ORA-00904: "OI"."INST_STATE_TXT": invalid identifier
ORA-02063: preceding line from CTPS3


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