程序执行报ORA-01858错误，SQL Developer中运行正常

问题描述 #

程序执行PL/SQL代码时抛出ORA-01858: 预期数字位置出现非数字字符错误，但在SQL Developer中直接运行同一代码却正常。

涉及的PL/SQL代码：

DECLARE
  
    l_prev_purchase_batch_id    xx_rep_status.reporting_status_id%type;
    l_tax_calendar_period       xx_rep_status.tax_calendar_period%type := 'JAN-06';
    
BEGIN
  
    SELECT  status.reporting_status_id          
      INTO  l_prev_purchase_batch_id
      FROM  xx_rep_status status
     WHERE  status.vat_reporting_entity_id = 300100097478219
       AND  TO_CHAR(TO_DATE(status.tax_calendar_period, 'MM/YY'), 'MM/YY') = TO_CHAR(ADD_MONTHS(TO_DATE(l_tax_calendar_period, 'MM/YY'), -1), 'MM/YY')
       AND  status.source NOT IN ('GL', 'AR', 'AP')
       AND  status.source = 'P2P';
       
END;

会话NLS参数对比 #

通过以下SQL查询会话与数据库的NLS参数：

select  VALUE, 'SESSION_NLS_DATE_FORMAT' parameter
from    nls_session_parameters
where   parameter = 'NLS_DATE_FORMAT'
UNION ALL
select  VALUE, 'DB_NLS_DATE_FORMAT'
from    nls_database_parameters
where   parameter = 'NLS_DATE_FORMAT'
UNION ALL
select  VALUE, 'SESSION_NLS_DATE_LANGUAGE'
from    nls_session_parameters
where   parameter = 'NLS_DATE_LANGUAGE'
UNION ALL
select  VALUE, 'DB_NLS_DATE_LANGUAGE'
from    nls_database_parameters
where   parameter = 'NLS_DATE_LANGUAGE';

SQL Developer会话参数 #

VALUE         PARAMETER                
------------- -------------------------
DD-MON-RR     SESSION_NLS_DATE_FORMAT  
DD-MON-RR     DB_NLS_DATE_FORMAT       
AMERICAN      SESSION_NLS_DATE_LANGUAGE
AMERICAN      DB_NLS_DATE_LANGUAGE     

应用程序会话参数 #

VALUE                       PARAMETER                
--------------------------- -------------------------
YYYY-MM-DD                  SESSION_NLS_DATE_FORMAT  
DD-MON-RR                   DB_NLS_DATE_FORMAT       
NUMERIC DATE LANGUAGE       SESSION_NLS_DATE_LANGUAGE
AMERICAN                    DB_NLS_DATE_LANGUAGE     

已知l_tax_calendar_period的值会动态变化，可能为'01-01'或'JAN-01'这类格式。

错误根源分析 #

问题出在会话的NLS_DATE_LANGUAGE差异：

• SQL Developer会话使用AMERICAN语言，能正确识别'JAN'这类英文月份缩写，执行TO_DATE('JAN-06', 'MM/YY')时不会报错。
• 应用程序会话使用NUMERIC DATE LANGUAGE，该模式下仅识别数字格式的月份（如'01'），无法解析'JAN'这类英文缩写，触发ORA-01858错误。

另外，代码中直接依赖会话NLS参数进行日期转换，属于非确定性转换，不同会话环境下行为不一致，是典型的隐患。

解决思路 #

1. 日期转换时显式指定NLS语言参数 #

在TO_DATE函数中添加第三个参数，强制指定日期解析的语言，不受会话NLS参数影响：

-- 解析英文月份格式时指定AMERICAN语言
TO_DATE(l_tax_calendar_period, 'MM/YY', 'NLS_DATE_LANGUAGE=AMERICAN')

同时对表字段的转换也做同样处理：

TO_DATE(status.tax_calendar_period, 'MM/YY', 'NLS_DATE_LANGUAGE=AMERICAN')

2. 兼容两种输入格式的转换逻辑 #

因为l_tax_calendar_period可能是数字格式（如'01-01'）或英文缩写格式（如'JAN-01'），可以编写兼容逻辑：

-- 使用CASE判断格式，分别处理
CASE 
    WHEN REGEXP_LIKE(l_tax_calendar_period, '^[A-Z]{3}-[0-9]{2}$', 'i') THEN
        TO_DATE(l_tax_calendar_period, 'MON-RR', 'NLS_DATE_LANGUAGE=AMERICAN')
    WHEN REGEXP_LIKE(l_tax_calendar_period, '^[0-9]{2}-[0-9]{2}$') THEN
        TO_DATE(l_tax_calendar_period, 'MM-RR')
    ELSE
        -- 处理非法格式的逻辑，比如抛出异常或返回默认值
        NULL
END

3. 统一存储与输入格式 #

如果可能，尽量将tax_calendar_period字段统一存储为**数字格式的字符串（如'01-06'）**或直接存储为DATE类型，从源头避免格式混乱。同时规范输入参数的格式，减少多格式兼容的复杂度。

4. 修改应用会话的NLS_DATE_LANGUAGE #

如果应用场景允许，可以在程序初始化时执行以下语句，将会话的NLS_DATE_LANGUAGE设置为AMERICAN：

ALTER SESSION SET NLS_DATE_LANGUAGE = 'AMERICAN';

但这种方式依赖会话配置，若应用是多租户或多语言场景，可能引发其他问题，需谨慎评估。

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