ORA-01719 outer join operator (+) not allowed in operand of OR or IN
来源:互联网 发布:日本找工作的软件 编辑:程序博客网 时间:2024/05/19 10:10
以下sql在9208中运行正常,升级到10.2.0.5后运行报错 ORA-01719 outer join operator (+) not allowed in operand of OR or IN
有通过db link连接到远端资料库。
SELECT BM.MOLD_CODE,P.PARTS_CODE,BD.USELESS_NUMBERS,BD.DEFECT_CODE
FROM B_C_HGMASTER@BOND BM,B_LINE@BOND BL,PARTS P,B_C_HGDETAIL@BOND BD
WHERE BM.FACTORY_CODE='AAA'
AND BM.JY_DATE>='2013/09/16'
AND BM.JY_DATE<='2013/09/21'
AND BM.MOLD_CODE='BBB-CCC'
AND BM.SECTION_CODE in('H3001','H3002','3001','3002')
AND BM.DATA_NAME=BD.DATA_NAME
AND BM.SECTION_CODE=BL.LINE_CODE(+)
AND BM.PARTS_CODE=P.PARTS_CODE(+)
---------------------------------------------------------------------
查找网络得到以下信息。
这是一个oracle bug修复造成的问题
以下是oracle support的回复
In Oracle 10.2.0.4 and before, there is a bug in "common subexpression elimination" function. The bug number is 5346187. It can lead wrong transformation.
It was fixed on 10.2.0.5.
If setting the hidden parameter "_eliminate_common_subexpr" to false to disable the "common subexpression elimination", the ORA-1719 error will be report.
Please refer Bug 5346187, Note 5346187.8 and the last test case.
We recommend you to contact your developer to modify the SQL statement.
For example,
Using "unoin all" instead of "OR" operator.
---------------------------------------------------------------------
悲催了,只有改sql了,把远端的3个表(这段代码当初就不合理,既然4个表中有3个是远端的,就应该先在远端处理完),先在远端作关联建立视图,然后再同本地关联。
create or replace view v_B_C_HGMASTER as
SELECT bm.factory_code,bm.section_code,BM.JY_DATE,BM.MOLD_CODE,BM.PARTS_CODE,BD.USELESS_NUMBERS,BD.DEFECT_CODE
FROM B_C_HGMASTER BM,B_LINE BL,B_C_HGDETAIL BD
WHERE BM.DATA_NAME=BD.DATA_NAME
AND BM.SECTION_CODE=BL.LINE_CODE(+)
---------------
SELECT BM.MOLD_CODE,P.PARTS_CODE,Bm.USELESS_NUMBERS,Bm.DEFECT_CODE FROM v_B_C_HGMASTER@bond bm,PARTS P
WHERE bm.PARTS_CODE=P.PARTS_CODE(+)
and BM.FACTORY_CODE='AAA
AND BM.JY_DATE>='2013/09/16'
AND BM.JY_DATE<='2013/09/21'
AND BM.MOLD_CODE='BBB-CCC'
AND BM.SECTION_CODE in('H3001','H3002','3001','3002')
-------同样ERROR--------
select e.employee_id, e.last_name, d.department_name
from employees e, departments dwhere e.department_id = d.department_id(+)
OR e.job_id = 'MGR';
- gson初步使用
- android使用工程指令:SECRET_CODE
- Javascript string.replace 方法中 函数参数的使用
- Windows下的Objective-C集成开发环境(IDE)
- 关于object-c
- ORA-01719 outer join operator (+) not allowed in operand of OR or IN
- 金山网络CEO 傅盛
- hdu 4751 Divide Groups 2013南京网络赛
- 常用算法设计方法
- paip.提升用户体验----c++ c# 配色方案
- Exception
- Sandcastle:开源C#文档生成工具(也可用于其他语言)
- FusionChart用XML和JSON两种格式提供数据源
- android 通过uri截取指定大小的图片显示在imageview上