Oracle MERGE插入TIMESTAMP字段日期值异常及ORA-01830报错解决

错误根因 #

• 隐式类型转换异常：存储过程形参in_data定义为TIMESTAMP类型，但调用时直接传入字符串'28.01.2022 14:07'，Oracle会依赖会话级默认NLS_TIMESTAMP_FORMAT参数做隐式转换，默认格式与传入的DD.MM.YYYY HH24:MI格式不匹配，最终导致年份、时间值解析错误。
• ORA-01830报错触发逻辑：你使用的转换格式串'DD.MM.YYYY HH24:MI:SS.FF'要求输入字符串包含秒、小数秒部分，但实际传入值仅精确到分钟，格式串与输入长度不匹配，直接触发转换报错。
• 原代码存在语法问题：存储过程参数列表中in_id IN NUMBER后缺少逗号分隔符，DML语句缺少结束分号，无法正常编译。

修复步骤 #

1. 将存储过程的入参in_data类型改为VARCHAR2，从入参层避免Oracle自动做隐式日期转换。
2. 在MERGE语句的数据源子查询中，使用和输入字符串完全匹配的格式掩码DD.MM.YYYY HH24:MI做显式TO_TIMESTAMP转换，Oracle会自动补全缺失的秒、6位小数秒为0，正好匹配预期的入库格式。
3. 补全所有缺失的语法分隔符，修正INSERT子句的字段引用别名，避免字段歧义。

修复后完整可执行代码 #

DECLARE
PROCEDURE settings_import
(
  in_id   IN NUMBER,
  in_data IN VARCHAR2 -- 改为字符串类型接收入参，避免隐式转换
)
IS
BEGIN
MERGE INTO settings a
USING (
  SELECT 
    in_id AS ID, 
    -- 用和输入完全匹配的格式做显式转换，自动补全秒和小数秒
    TO_TIMESTAMP(in_data, 'DD.MM.YYYY HH24:MI') AS data 
  FROM dual
) b
ON (a.ID = b.ID)
WHEN NOT MATCHED THEN
  INSERT (ID, data) VALUES (b.ID, b.data)
WHEN MATCHED THEN
  UPDATE SET a.data = b.data;
END settings_import;
BEGIN
  settings_import (12, '28.01.2022 14:07');
END;
/

可选替代方案（不修改存储过程参数） #

如果不想调整存储过程参数定义，只要在调用存储过程时提前做显式类型转换，不要直接传入字符串即可，调用写法如下：

BEGIN
  settings_import (
    12, 
    TO_TIMESTAMP('28.01.2022 14:07', 'DD.MM.YYYY HH24:MI')
  );
END;
/

上述两种方案执行后，settings.data字段入库值均为预期的28.01.2022 14:07:00,000000，不会出现时间偏移、年份错误问题。

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