ORA-00918 AMBIGUOUS COLUMN ERROR
时间：2010-05-30 来源：rushmeplz
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
修改时间 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
相关阅读 更多 +
