Oracle Connect By Level生成日期遇ORA-01861错误求助

核心原因 #

你遇到的ORA-01861错误，大概率是参数类型与to_date函数的使用不匹配导致：

• 如果Oracle Fusion中绑定的:p_from_date和:p_to_date是日期类型参数，你用to_date()转换它时，Oracle会先将日期值隐式转为字符串（遵循当前会话的NLS_DATE_FORMAT格式），再尝试用dd-mm-yyyy格式转回日期。一旦会话默认格式和你指定的格式不一致，就会触发格式不匹配错误。
• 另外原SQL写法存在逻辑问题：将返回多行的子查询直接放在SELECT列表中，后续还会触发ORA-01427: 单行子查询返回多个行错误。

修正方案 #

情况1：参数是日期类型（推荐） #

直接使用日期值计算，无需to_date转换：

SELECT papf.person_number emp_id,
       date_seq.dt
FROM   papf
-- 请补充员工表与日期序列的业务关联条件，避免产生笛卡尔积
CROSS JOIN (
    SELECT :p_from_date + LEVEL - 1 dt
    FROM   dual
    CONNECT BY LEVEL <= (:p_to_date - :p_from_date + 1)
) date_seq

情况2：参数确实是字符串类型 #

显式指定日期语言，避免会话语言差异导致的格式解析错误：

SELECT papf.person_number emp_id,
       date_seq.dt
FROM   papf
CROSS JOIN (
    SELECT TO_DATE(:p_from_date, 'DD-MM-YYYY', 'NLS_DATE_LANGUAGE=ENGLISH') + LEVEL - 1 dt
    FROM   dual
    CONNECT BY LEVEL <= (
        TO_DATE(:p_to_date, 'DD-MM-YYYY', 'NLS_DATE_LANGUAGE=ENGLISH') 
        - TO_DATE(:p_from_date, 'DD-MM-YYYY', 'NLS_DATE_LANGUAGE=ENGLISH') 
        + 1
    )
) date_seq

额外优化提示 #

如果日期范围较大（比如超过1000天），Oracle 12cR2及以上版本可以用更简洁的GENERATE_SERIES写法：

SELECT papf.person_number emp_id,
       :p_from_date + s.rn - 1 dt
FROM   papf
CROSS JOIN (
    SELECT ROWNUM rn
    FROM   GENERATE_SERIES(1, (:p_to_date - :p_from_date + 1))
) s

内容的提问来源于stack exchange，提问作者Aasem Shoshari