如何解决Oracle插入JSON数据时的ORA-01861格式不匹配错误？

错误原因 #

你的插入语句最后一步属于冗余操作：在SELECT ID,TO_DATE(CREATEDDATE ,'DD-MON-YYYY HH24:MI:SS')中，CREATEDDATE在CODES子查询里已经被转换为DATE类型。当对DATE类型调用TO_DATE时，Oracle会先将该DATE隐式转换为字符串（遵循当前会话的NLS_DATE_FORMAT参数），再尝试用你指定的格式解析这个字符串。由于隐式转换后的字符串格式与你指定的格式不匹配，直接触发了ORA-01861错误。

修复方案 #

方案1：移除多余的TO_DATE转换 #

这是最直接的修复方式，因为CODES子查询已经输出合法的DATE类型，直接插入即可：

INSERT INTO MATERIAL_JSON_DECODE(ID , CREATED_DATE)
WITH CODES AS 
(
 SELECT ID,
    CAST(TO_TIMESTAMP_TZ(createdDate, 'FXYYYY-MM-DD"T"HH24:MI:SS.FXFF3"Z"') AT LOCAL AS DATE) createdDate
    FROM 
    (
    SELECT DISTINCT 
     ID,createdDate
    FROM
    MATERIAL_T D,
    JSON_TABLE
    (
       D.MESSAGE_VALUE, '$' COLUMNS
       (
         ID VARCHAR2(6) PATH '$._id',
         createdDate VARCHAR2(100) PATH '$.createdDate'
        
     )   
  )
  )
  )
SELECT ID, CREATEDDATE AS CREATED_DATE FROM CODES ;

方案2：简化转换逻辑（推荐） #

可以在JSON_TABLE中直接将JSON日期字符串转换为DATE类型，省去中间子查询的冗余转换，让代码更简洁高效：

INSERT INTO MATERIAL_JSON_DECODE(ID , CREATED_DATE)
SELECT DISTINCT 
  ID,
  CAST(TO_TIMESTAMP_TZ(createdDate, 'FXYYYY-MM-DD"T"HH24:MI:SS.FXFF3"Z"') AT LOCAL AS DATE) AS CREATED_DATE
FROM MATERIAL_T D,
JSON_TABLE
(
   D.MESSAGE_VALUE, '$' COLUMNS
   (
     ID NUMBER(5) PATH '$._id', -- 直接转换为NUMBER类型匹配目标表定义
     createdDate VARCHAR2(100) PATH '$.createdDate'
   )   
);

这里额外优化了ID的类型转换，直接在JSON_TABLE中转为NUMBER(5)，避免后续隐式转换可能带来的问题。

转换逻辑说明 #

你的JSON日期2023-12-08T12:25:36.686Z是标准ISO8601 UTC格式，使用TO_TIMESTAMP_TZ配合格式串'FXYYYY-MM-DD"T"HH24:MI:SS.FXFF3"Z"'可以精准解析：

• FX强制严格匹配格式，避免宽松解析导致的异常
• FF3匹配毫秒部分
• AT LOCAL将UTC时间转换为数据库服务器的本地时区日期

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