ORA-0904 when Selecting from a View based on function that uses a Database LInk (Doc ID 1297919.1)

Last updated on FEBRUARY 06, 2017

Applies to:

Oracle Server - Enterprise Edition - Version: 10.1.0.2 to 11.1.0.7 - Release: 10.1 to 11.1
Information in this document applies to any platform.

Symptoms


Select from view that calls a function with a dblink fails with ORA-0904.
For instance:



--Database B
connect user1/pwd@db102b
create function test_fnc return number is
  begin
  return 1;
end;
/

--Database A
connect user2/pwd@db102a
create database link db102b connect to user1 identified by user1 using 'db102b';

create view test_view as select test_fnc@db102b from dual;

select * from test_view;
*
ERROR at line 1:
ORA-904 : "TEST_FNC": invalid identifier

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