sql - 错误报告 - SQL Error: ORA-01722: invalid number in Oracle sql developer tool

Question

甲骨文查询：

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
);

Oracle SQLDeveloper 中的错误消息：

错误报告 - SQL 错误：ORA-01722：无效号码 01722。00000 -“无效号码” *原因：指定的号码无效。*行动：指定一个有效的数字。

当我尝试在 Toad 中运行相同的查询时，它会在没有任何错误的情况下执行，但是当我在 oracle sql developer tool 中尝试相同的查询时，会遇到上述错误。

我也试图通过在excel中运行查询VB script并得到相同的错误。请帮助我解决问题。

标签： sqloraclevbscript