ORA-30007: CONNECT BY ROOT operator is not supported in the START WITH or in the CONNECT BY conditio

文档解释

ORA-30007: CONNECT BY ROOT operator is not supported in the START WITH or in the CONNECT BY condition

Cause: An attempt was made to use CONNECT BY ROOT operator in the START WITH or in the CONNECT BY condition.

Action: Remove CONNECT BY ROOT operator from the START WITH or from the CONNECT BY condition.

Oracle 数据库中出现 ORA-30007：CONNECT BY ROOT 操作符在 START WITH 或 CONNECT BY 条件中不受支持 的报错时，表明有在 START WITH 或 CONNECT BY 条件中应用CONNECT BY ROOT 操作符。

官方解释

由于 CONNECT BY ROOT 操作符不支持在 START WITH 条件或 CONNECT BY 条件中使用，所以可以避免此 ORA-30007 错误。

常见案例

这个错误常常发生在写入Hierarchical Queries的SQL文，特别是在写入下面的SQL语句的时：

SELECT *

FROM employee e

START WITH ROOT = ‘JOHN’

CONNECT BY PRIOR e.manager_id = e.emp_id

ORDER BY e.emp_name;

一般处理方法及步骤

如果想要避免该错误，只需要将“ROOT =”在START WITH 条件中替换为“employee_name =”或“employee_id =”就可以了。

例如：

SELECT *

FROM employees e

START WITH e.employee_name = ‘John’

CONNECT BY PRIOR e.manager_id = e.employee_id

ORDER BY e.employee_name;