问题现象

-- 创建表
DROP TABLE EMPLOYEES;
CREATE TABLE EMPLOYEES (
    EMPLOYEEID NUMBER PRIMARY KEY,
    EMPLOYEENAME VARCHAR2(100),
    MANAGERID NUMBER NULL
); 
-- 插入数据
BEGIN
    FOR I IN 1..102 LOOP
        IF I = 1 THEN
            INSERT INTO EMPLOYEES (EMPLOYEEID, EMPLOYEENAME, MANAGERID)
            VALUES (I, 'EMPLOYEE ' || I, NULL);
        ELSE
            INSERT INTO EMPLOYEES (EMPLOYEEID, EMPLOYEENAME, MANAGERID)
            VALUES (I, 'EMPLOYEE ' || I, I - 1);
        END IF;
    END LOOP;
    COMMIT;
END;
/

WITH EMPLOYEEHIERARCHY (EMPLOYEEID, EMPLOYEENAME, MANAGERID, LEVEL) AS (
    SELECT EMPLOYEEID, EMPLOYEENAME, MANAGERID, 1 AS LEVEL
    FROM EMPLOYEES
    WHERE MANAGERID IS NULL

    UNION ALL

    SELECT E.EMPLOYEEID, E.EMPLOYEENAME, E.MANAGERID, EH.LEVEL + 1
    FROM EMPLOYEES E
    JOIN EMPLOYEEHIERARCHY EH ON E.MANAGERID = EH.EMPLOYEEID
)
SELECT  COUNT(DISTINCT EMPLOYEEID) 
FROM EMPLOYEEHIERARCHY;

执行sql语句报错如下。

问题分析

DM默认允许CTE递归最多100次，如果递归层次超过了这个默认值，就会出现 -2270 错误。这是为了防止查询陷入无限递归，导致数据库资源耗尽。

解决方法

1. 优化查询逻辑： 确保查询逻辑合理，避免不必要的深度递归。

2. 增加递归深度限制： 通过加 HINT 设置一个更大的 MAXRECURSION 值来允许更多的递归迭代。