ORA-00918 AMBIGUOUS COLUMN ERROR

Outer Join Returns Ambiguous Column Error ORA-00918 on 10.2 [ID 337279.1]  

--------------------------------------------------------------------------------
 
  修改时间 11-MAR-2009     类型 PROBLEM     状态 PUBLISHED  

In this Document
  Symptoms
  Cause
  Solution
  References

 

--------------------------------------------------------------------------------

 

 

Applies to:
Oracle Server Enterprise Edition - Version: 10.2.0.1
This problem can occur on any platform.
Oracle Server - Enterprise Edition - Version: 10.2.0.1
Symptoms
Executing a select with column names using FULL OUTER JOIN produces an ORA-00918 error on 10.2.0.1, but does not produce the error on either 9i (9.2.0.6) or 10gR1 version 10.1.0.2.

-- repro case
-- create the table
create table foj_table (STR_38 varchar2(20), NUM_35 number, STR_33
varchar2(20));
-- create a view based on the table
create or replace view foj  as
SELECT
       STR_38 AS priority,
       NUM_35 AS totalPrice,
       STR_33 AS requestId
FROM foj_table;
-- this select statement incorrectly gives the ORA-00918 error
SELECT p.priority
FROM
       foj p FULL OUTER JOIN foj f ON p.requestId = f.requestId;
-- this select statement does not produce the error
SELECT *
FROM
       foj p FULL OUTER JOIN foj f ON p.requestId = f.requestId;
Cause
This issue is due to the following bug:
Bug 4655164 OUTER JOIN RETURNS AMBIGUOUS COLUMN ERROR ORA-918
Solution
1) Create an after logon trigger as sys user as follow:

2) Run the affected query.

Example output:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> show parameter _column_elimination_off

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
_column_elimination_off              boolean     TRUE

SQL> SELECT p.priority
FROM  foj p FULL OUTER JOIN foj f ON p.requestId = f.requestId;

no rows selected

create or replace trigger tg_column_elimination after logon on database
  begin
    execute immediate 'alter session set "_column_elimination_off"=true';
    execute immediate 'alter system flush shared_pool';
  end;
  /

References
BUG:4655164 - OUTER JOIN RETURNS AMBIGUOUS COLUMN ERROR ORA-918
BUG:6319169 - ORA-918 ON FULL OUTER JOIN

注:遇到此bug发生平台为10.2.0.4 RAC AIX PLATFORM