NUMTODSINTERVAL()传入超90分钟数值报ORA-01481错误如何解决

ORA-01481错误根因 #

报错是由SQL中TO_CHAR(f.Duration, 'hh24:mi')、TO_CHAR(f2.Duration, 'hh24:mi')两行代码的错误用法导致的：

• f.Duration字段为数值类型，存储的是影片时长对应的分钟数
• hh24:mi是Oracle中日期/时间间隔类型专用的格式掩码，不能直接用于数值类型转换
• 当时长超过99分钟（数值变为3位数）时，格式匹配失败抛出ORA-01481错误

修复方案 #

有两种常用修正方式，二选一即可：

方案1：将数值转为时间间隔后格式化

把分钟数先转为时间间隔类型，再用hh24:mi掩码格式化，修改对应两行代码即可：

-- 原错误写法
TO_CHAR(f.Duration, 'hh24:mi')
-- 修改为
TO_CHAR(NUMTODSINTERVAL(f.Duration, 'MINUTE'), 'hh24:mi')

-- 同理f2的时长字段也做同样修改
TO_CHAR(f2.Duration, 'hh24:mi')
-- 修改为
TO_CHAR(NUMTODSINTERVAL(f2.Duration, 'MINUTE'), 'hh24:mi')

方案2：手动计算拼接时长格式

兼容性更强，无需依赖时间间隔类型转换，直接通过数值计算拼接出小时:分钟格式：

-- 替换f.Duration的转换代码
TRUNC(f.Duration/60)||':'||LPAD(MOD(f.Duration,60),2,'0')
-- 替换f2.Duration的转换代码
TRUNC(f2.Duration/60)||':'||LPAD(MOD(f2.Duration,60),2,'0')

修正后完整SQL（采用方案1示例） #

SELECT f.Name,
       TO_CHAR(s.Film_Start_Time, 'hh24:mi'),
       TO_CHAR(NUMTODSINTERVAL(f.Duration, 'MINUTE'), 'hh24:mi'),
       f2.Name,
       TO_CHAR(s2.Film_Start_Time, 'hh24:mi'),
       TO_CHAR(NUMTODSINTERVAL(f2.Duration, 'MINUTE'), 'hh24:mi')
FROM   Schedule s
       JOIN Films f ON s.Film_Name = f.Name
       JOIN Schedule s2 ON s2.Film_Start_Time > s.Film_Start_Time
                       AND s2.Film_Start_Time < s.Film_Start_Time + NUMTODSINTERVAL(f.Duration, 'MINUTE')
       JOIN Films f2 ON s2.Film_Name = f2.Name
ORDER BY s.Film_Start_Time ASC;

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