Oracle SQL执行报错ORA-01722：多工具执行结果不一致求助

问题背景 #

我在执行一段Oracle查询时遇到了头疼的问题：同样的SQL在Toad里跑完全正常，但在Oracle SQL Developer和Excel VB脚本里执行就抛出ORA-01722: invalid number错误。具体信息如下：

执行的Oracle查询 #

select job_name,Status,rundate,starttime,endtime,next_start,start_times,start_mins,max_run_alarm, 
case when endtime is not null and starttime is not null then to_date(endtime,'DD-MON-YYYY HH24:MI:SS') - to_date(starttime,'DD-MON-YYYY HH24:MI:SS') end data 
from ( 
select Distinct a.job_name, a.description description,
Decode (job_type,98,'Box',99,'Command Job',102,'File watcher job',job_type) job_type, 
substr(decode(d.status,1,'Running', 3,'Starting', 4,'Success', 5,'Failed', 6,'Terminated', 7,'On Ice', 8,'Inactive', 9,'Activated', 11,'On Hold', 12,'Que Wait', d.status),1,9) status, 
decode(c.ENDTIME,0,null,999999999,null, 
to_char(FROM_TZ(TO_TIMESTAMP(TO_CHAR(TO_DATE(2440588 + TRUNC((c.ENDTIME + (replace(sessiontimezone,':','.')*3600)) / 86400 ),'J'),'ddmmyyyy') ||LPAD(((c.ENDTIME + (replace(sessiontimezone,':','.')*3600))- (TRUNC((c.ENDTIME + (replace(sessiontimezone,':','.')*3600)) / 86400) * 86400 )),5,0), 'ddmmyyyysssss'),sessiontimezone)- to_dsinterval('0 00:00:00'), --'US/Eastern' 
'dd-Mon-yyyy')) RUNDATE, 
(to_char(FROM_TZ(TO_TIMESTAMP(TO_CHAR(TO_DATE(2440588 + TRUNC((c.STARTIME + (replace(sessiontimezone,':','.')*3600)) / 86400 ),'J'),'ddmmyyyy') ||LPAD(((c.STARTIME + (replace(sessiontimezone,':','.')*3600))- (TRUNC((c.STARTIME + (replace(sessiontimezone,':','.')*3600)) / 86400) * 86400 )),5,0), 'ddmmyyyysssss'),sessiontimezone)- to_dsinterval('0 00:00:00'), --'US/Eastern' 
'dd-Mon-yyyy hh24:mi:ss')) starttime, 
decode(c.ENDTIME,0,null,999999999,null, 
to_char(FROM_TZ(TO_TIMESTAMP(TO_CHAR(TO_DATE(2440588 + TRUNC((c.ENDTIME + (replace(sessiontimezone,':','.')*3600)) / 86400 ),'J'),'ddmmyyyy') ||LPAD(((c.ENDTIME + (replace(sessiontimezone,':','.')*3600))- (TRUNC((c.ENDTIME + (replace(sessiontimezone,':','.')*3600)) / 86400) * 86400 )),5,0), 'ddmmyyyysssss'),sessiontimezone)- to_dsinterval('0 00:00:00'), --'US/Eastern' 
'dd-Mon-yyyy hh24:mi:ss')) endtime, 
decode(d.next_start,0,null,999999999,null, 
to_char(FROM_TZ(TO_TIMESTAMP(TO_CHAR(TO_DATE(2440588 + TRUNC((d.next_start + (replace(sessiontimezone,':','.')*3600)) / 86400 ),'J'),'ddmmyyyy') ||LPAD(((d.next_start + (replace(sessiontimezone,':','.')*3600))- (TRUNC((d.next_start + (replace(sessiontimezone,':','.')*3600)) / 86400) * 86400 )),5,0), 'ddmmyyyysssss'),sessiontimezone)- to_dsinterval('0 00:00:00'), --'US/Eastern' 
'dd-Mon-yyyy hh24:mi:ss')) next_start, 
a.mach_name,a.owner,g.command,g.std_err_file,g.std_out_file,f.days_of_week,f.start_times,f.start_mins,f.run_calendar,f.max_run_alarm,profile 
from ujo_job a, ujo_job_runs c, ujo_job_status d, 
(select joid,max(STARTIME) startime, max(endtime) endtime from ujo_job_runs group by joid) e, 
ujo_command_job g, ujo_sched_info f 
where a.joid = c.joid(+) and a.joid = d.joid(+) and a.joid = e.joid(+) and a.joid = f.joid(+) and a.joid = g.joid(+) 
and (c.startime = e.startime or c.startime is null) 
and job_name ='v_job_name' and a.is_active =1 ); 

错误详情 #

在SQL Developer和VB脚本中执行时，收到的错误提示：

Error report - SQL Error: ORA-01722: invalid number
01722. 00000 - "invalid number"
*Cause: The specified number was invalid.
*Action: Specify a valid number.

问题根源分析 #

我处理过很多次这种跨工具的ORA-01722问题，核心原因基本都是会话参数差异或者隐式类型转换踩坑，结合你的场景来看：

1. 时区偏移转换的隐患（最可能的元凶） #

你的查询里多次用了replace(sessiontimezone,':','.')*3600，目的是把时区格式（比如-04:00）转成数字再算秒数。但这里有个大问题：Oracle的数字解析依赖NLS_NUMERIC_CHARACTERS参数——如果这个参数设置的是,.（逗号当小数点），那么replace出来的-04.00会被当成无效数字，因为Oracle期望小数点是逗号。

Toad大概率默认的NLS_NUMERIC_CHARACTERS是.,（点做小数点），而SQL Developer和VB脚本的会话设置是,.，这就导致了工具间的执行差异。

2. decode里的隐式转换风险 #

比如decode(c.ENDTIME,0,null,999999999,null,...)，如果c.ENDTIME是字符串类型，当它和数字0/999999999比较时，Oracle会自动把字符串转成数字。如果表里有非数字的ENDTIME值，Toad可能刚好没扫到这些数据（比如执行计划不同），而SQL Developer/VB脚本触发了全表扫描，碰到了脏数据就报错。

3. case语句的日期转换 #

虽然case里的to_date报错通常是ORA-01830，但如果endtime/starttime的格式有问题，也可能间接触发隐式转换导致ORA-01722，不过这个概率相对低一些。

解决方案 #

方案1：彻底修复时区转换逻辑（优先推荐） #

别再用replace这种字符串处理方式了，直接用Oracle内置函数提取时区偏移，完全不受NLS参数影响：

-- 把所有 replace(sessiontimezone,':','.')*3600 替换成下面的代码
EXTRACT(TIMEZONE_HOUR FROM SESSIONTIMEZONE)*3600 + EXTRACT(TIMEZONE_MINUTE FROM SESSIONTIMEZONE)*60

这个方法直接提取时区的小时和分钟，转成秒数，逻辑更清晰，也不会有转换失败的问题。

方案2：统一会话的数值格式参数 #

在查询开头加上这条语句，强制设置数值格式为点做小数点：

ALTER SESSION SET NLS_NUMERIC_CHARACTERS = '.,';

注意：VB脚本执行时，要么在连接字符串里指定这个参数，要么先执行这条ALTER语句再跑查询。

方案3：检查脏数据 #

如果ujo_job_runs表的ENDTIME/STARTIME是字符串类型，先查一下有没有非数字的记录：

-- 检查ENDTIME是否有非数字内容
SELECT ENDTIME FROM ujo_job_runs WHERE NOT REGEXP_LIKE(ENDTIME, '^[0-9]+$');
-- 检查STARTIME是否有非数字内容
SELECT STARTIME FROM ujo_job_runs WHERE NOT REGEXP_LIKE(STARTIME, '^[0-9]+$');

如果发现脏数据，要么清理掉，要么在查询里加过滤条件（比如WHERE REGEXP_LIKE(ENDTIME, '^[0-9]+$')，前提是业务允许）。

方案4：显式转换并处理错误（Oracle 12c+适用） #

在decode里用TO_NUMBER的容错转换，避免转换失败报错：

-- 原代码：decode(c.ENDTIME,0,null,999999999,null,...)
-- 修改为：
decode(TO_NUMBER(c.ENDTIME DEFAULT NULL ON CONVERSION ERROR),0,null,999999999,null,...)

这个语法会在字符串转数字失败时返回null，而不是抛出ORA-01722错误。

验证步骤 #

建议按这个顺序排查：

1. 先试方案1的时区转换修复，这是最彻底解决工具差异的方法；
2. 如果问题还存在，用方案3的SQL检查是否有脏数据；
3. 最后可以试试方案2的会话参数设置，确保所有环境的NLS参数一致。

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