My Oracle Support Banner

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

Last updated on FEBRUARY 02, 2022

Applies to:

Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Cloud Service - Version N/A and later
Oracle Database - Enterprise Edition - Version to [Release 10.2 to 11.1]
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Information in this document applies to any platform.


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

select *
from ctps3.institutions@<dblink> 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 <dblink>


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

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

In this Document

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