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: 10.2.0.5 and later   [Release: 10.2 and later ]
Information in this document applies to any platform.
***Checked for relevance on 14-Feb-2012***

Symptoms


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);
begin
if text_ = 'One' then
result_ := '(1) one';
else
result_ := 'some value';
end if;
return(result_);
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
/

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