Oracle Apex调用存储过程时日期验证触发ORA-06502错误

问题场景 #

在Oracle Apex环境中，使用硬编码日期调用存储过程时可正常运行，但使用页面项:P6_ANN_DATE进行日期转换后触发上述错误。

正常运行代码 #

-- Works fine 
DECLARE
  PARAMS SCHEDULER_ARGS := SCHEDULER_ARGS(BATCH_RUN_NO    => :P6_ANNIV_DATE,
                                          BATCH_DETAIL_NO => :P6_BATCH_DETAIL_NO,
                                          ANNIV_DATE      =>  to_date('01-12-2024','DD-MM-YYYY'),
                                          POLICY_NO       => :P6_POLICY_NO,
                                          NO_OF_THREADS   => :P6_NO_OF_THREADS,
                                          THREAD_NO       => :P6_THREAD_NO,
                                          ENTITY_NO       => :P6_ENTITY_NO,
                                          PROCESS_NO      => :P6_PROCESS_NO,
                                          ANNIV_RECALC    => :P6_ANNIV_RECALC,
                                          PROC_DESCR      => :P6_PROC_DESCR,
                                          OVERRIDE_DATE   => :P6_OVERRIDE_DATE);
BEGIN
  l_pkg_scheduler_utility.run_batch_threads(params => params);
END;

触发错误代码 #

DECLARE
  PARAMS SCHEDULER_ARGS := SCHEDULER_ARGS(BATCH_RUN_NO    => :P6_ANNIV_DATE,
                                          BATCH_DETAIL_NO => :P6_BATCH_DETAIL_NO,
                                          ANNIV_DATE      =>  to_char(to_date(:P6_ANN_DATE, 'DD-MM-YYYY'), 'DD-MM-YYYY'),
                                          POLICY_NO       => :P6_POLICY_NO,
                                          NO_OF_THREADS   => :P6_NO_OF_THREADS,
                                          THREAD_NO       => :P6_THREAD_NO,
                                          ENTITY_NO       => :P6_ENTITY_NO,
                                          PROCESS_NO      => :P6_PROCESS_NO,
                                          ANNIV_RECALC    => :P6_ANNIV_RECALC,
                                          PROC_DESCR      => :P6_PROC_DESCR,
                                          OVERRIDE_DATE   => :P6_OVERRIDE_DATE);
BEGIN
  l_pkg_scheduler_utility.run_batch_threads(params => params);
END;

错误原因 #

1. SCHEDULER_ARGS类型中的ANNIV_DATE参数定义为DATE类型，正常代码中通过to_date()生成DATE类型值赋值，完全匹配参数类型要求。
2. 错误代码中使用to_char(to_date(:P6_ANN_DATE, 'DD-MM-YYYY'), 'DD-MM-YYYY')生成了字符串类型值，赋值给DATE类型参数时，Oracle会触发隐式转换逻辑。
3. 隐式转换依赖当前会话的日期格式设置，如果会话日期格式与字符串格式不匹配，就会触发「字符转数字」的转换错误（ORA-06502）。

解决方案 #

方案1：直接传递DATE类型值 #

去掉多余的to_char()转换，直接将:P6_ANN_DATE转换为DATE类型赋值：

DECLARE
  PARAMS SCHEDULER_ARGS := SCHEDULER_ARGS(BATCH_RUN_NO    => :P6_ANNIV_DATE,
                                          BATCH_DETAIL_NO => :P6_BATCH_DETAIL_NO,
                                          ANNIV_DATE      =>  to_date(:P6_ANN_DATE, 'DD-MM-YYYY'),
                                          POLICY_NO       => :P6_POLICY_NO,
                                          NO_OF_THREADS   => :P6_NO_OF_THREADS,
                                          THREAD_NO       => :P6_THREAD_NO,
                                          ENTITY_NO       => :P6_ENTITY_NO,
                                          PROCESS_NO      => :P6_PROCESS_NO,
                                          ANNIV_RECALC    => :P6_ANNIV_RECALC,
                                          PROC_DESCR      => :P6_PROC_DESCR,
                                          OVERRIDE_DATE   => :P6_OVERRIDE_DATE);
BEGIN
  l_pkg_scheduler_utility.run_batch_threads(params => params);
END;

方案2：利用Apex页面项的原生类型 #

如果:P6_ANN_DATE的页面项类型是Date，Apex会自动将其作为DATE类型绑定变量传递，无需手动转换：

DECLARE
  PARAMS SCHEDULER_ARGS := SCHEDULER_ARGS(BATCH_RUN_NO    => :P6_ANNIV_DATE,
                                          BATCH_DETAIL_NO => :P6_BATCH_DETAIL_NO,
                                          ANNIV_DATE      => :P6_ANN_DATE,
                                          POLICY_NO       => :P6_POLICY_NO,
                                          NO_OF_THREADS   => :P6_NO_OF_THREADS,
                                          THREAD_NO       => :P6_THREAD_NO,
                                          ENTITY_NO       => :P6_ENTITY_NO,
                                          PROCESS_NO      => :P6_PROCESS_NO,
                                          ANNIV_RECALC    => :P6_ANNIV_RECALC,
                                          PROC_DESCR      => :P6_PROC_DESCR,
                                          OVERRIDE_DATE   => :P6_OVERRIDE_DATE);
BEGIN
  l_pkg_scheduler_utility.run_batch_threads(params => params);
END;

额外检查点 #

• 确认页面项:P6_ANN_DATE的输入格式与to_date()中指定的'DD-MM-YYYY'一致，避免因输入格式错误导致转换失败。
• 优先使用显式转换逻辑，减少对会话NLS_DATE_FORMAT设置的依赖，避免环境差异引发的问题。

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