My Oracle Support Banner

Full Outer Join Fails with ORA-904 While Selecting From a View (Doc ID 1406893.1)

Last updated on JULY 05, 2017

Applies to:

PL/SQL - Version: and later   [Release: 10.2 and later ]
Information in this document applies to any platform.
***Checked for relevance on 14-Feb-2012***


FULL OUTER JOIN fails with ORA-00904 error when used on views that access PL/SQL objects that are not in the current users schema

ERROR at line 2:
ORA-00904: "GET_DESCRIPTION": invalid identifier

The same query works perfect if the FULL OUTER JOIN is replaced with LEFT OUTER JOIN or RIGHT OUTER JOIN.

Testcase :

1) Connect as obj_owner and create the needed pl/sql function, sample table/view ..

connect obj_owner/pass;

create or replace function get_description(text_ IN VARCHAR2) return varchar2 is
result_ varchar2(2000);
if text_ = 'One' then
result_ := '(1) one';
result_ := 'some value';
end if;
end get_description;

create table key_table(key number primary key)
insert into key_table values (1)
insert into key_table values (2)
insert into key_table values (3)
create table data_table(key number primary key, text varchar2(100))
insert into data_table values (1, 'One')
insert into data_table values (4, 'Four')
insert into data_table values (5, 'Five')

create view key_view as select key from key_table
create view data_view as select key, text, get_description(text) description from data_table
grant select on key_view to app_user
grant select on data_view to app_user

2) As a different user try to select from the view

connect app_user/pass;

select *
from obj_owner.key_view k
full outer join
obj_owner.data_view d
on k.key = d.key


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.