升级到ORACLE 12c后不允许外连接运算符 ORA-30563

刘泽宇

那些使用外连接运算符（+）和CONNECT BY和ORDER BY子句的查询遇到ORA-30563

例子:

SQL> SELECT markfor
FROM
(SELECT
nvl(fdst.short_text,' ') markfor,
wlr.LABEL_REQUEST_ID
FROM
WMS_LABEL_REQUESTS wlr,
wsh_deliverables_v wdv,
<...snipped...>
AND ROWNUM=1 ;
ORDER BY fad.last_update_date(+) desc
*
ERROR at line 32:
ORA-30563: outer join operator (+) is not allowed here
或

SQL> select * from
2 (
3 select distinct
<...snipped...>
151 where seq=cnt start with seq=1 connect by prior seq+1=seq
152 and prior user_key=user_key(+) ) M
153 where user_details.USER_KEY = M.USER_KEY(+)
154 AND USER_DETAILS.USER_LEVEL_TYPE_CD not in (2,4)
155 )
156 order by 3 ;
and prior user_key=user_key(+) ) M
*
ERROR at line 152:
ORA-30563: outer join operator (+) is not allowed here



原因

数据库升级到12c


这是12c中的预期行为


解决方案

重写查询并从connect by/order子句中删除（+）。