ORA-01830错误求助：PLSQL插入XML数据时日期格式转换异常

我编写了如下PL/SQL插入语句，尝试将指定XML字符串数据插入到TAB_THD_ATTND_EVENTS表中，但触发了ORA-01830: date format picture ends before converting entire input string错误，恳请帮忙排查解决。

错误的PL/SQL代码 #

INSERT INTO TAB_THD_ATTND_EVENTS(
      ATND_EVNT_INDEXNO,            ATND_EVNT_USERID,             ATND_EVNT_USERNAME,           ATND_EVNT_DT,
      ATND_EVNT_ENEX_TYPE,          ATTND_EVNT_MSTR_CNTRLID,      ATTND_EVNT_DOOR_CNTRLID,      ATTND_EVNT_SPCL_FNCTNID,
      ATTND_EVNT_LEAVE_DT,          ATTND_EVNT_INSERT_DT,         ATTND_EVNT_PRCS_FLAG,         ATTND_EVNT_PRCS_DT,
      CR_DATE
 )
 SELECT 
      X.ATND_EVNT_INDEXNO,          X.ATND_EVNT_USERID,           X.ATND_EVNT_USERNAME,         TO_DATE(X.ATND_EVNT_DT,'DD/MM/YYYY HH24:MI:SS'),
      X.ATND_EVNT_ENEX_TYPE,        X.ATTND_EVNT_MSTR_CNTRLID,    X.ATTND_EVNT_DOOR_CNTRLID,    X.ATTND_EVNT_SPCL_FNCTNID,
      X.ATTND_EVNT_LEAVE_DT,        TO_DATE(X.ATTND_EVNT_INSERT_DT,'MM/DD/YYYY HH24:MI:SS'),    'N',      '',
      SYSDATE 
 FROM TAB_TDL_ATTND_UPLOAD_TEMP T,
      (XMLTABLE('/DocumentElement/event-ta' PASSING T.ATTND_DATA_XML COLUMNS
                     ATND_EVNT_INDEXNO NUMBER PATH './IndexNo',
                     ATND_EVNT_USERID NUMBER PATH './UserID',
                     ATND_EVNT_USERNAME VARCHAR2(100) PATH './UserName',
                     ATND_EVNT_DT DATE PATH './EventDateTime',
                     ATND_EVNT_ENEX_TYPE NUMBER PATH './EntryExitType',
                     ATTND_EVNT_MSTR_CNTRLID NUMBER PATH './MasterControllerID',
                     ATTND_EVNT_DOOR_CNTRLID NUMBER PATH './DoorControllerID',
                     ATTND_EVNT_SPCL_FNCTNID NUMBER PATH './SpecialFunctionID',
                     ATTND_EVNT_LEAVE_DT DATE PATH './LeaveDT',
                     ATTND_EVNT_INSERT_DT DATE PATH './IDateTime')) X
 WHERE T.ATTND_UPLOAD_NO = P_SEQNO;

待插入的XML数据 #

<event-ta>
<IndexNo>85672</IndexNo>
<UserID>1001</UserID>
<UserName>Testing Data</UserName>
<EventDateTime>17/04/2023 08:08:50</EventDateTime>
<EntryExitType>0</EntryExitType>
<MasterControllerID>34</MasterControllerID>
<DoorControllerID>1</DoorControllerID>
<SpecialFunctionID>0</SpecialFunctionID>
<LeaveDT/>
<IDateTime>04/17/2023 08:08:53</IDateTime>
</event-ta>

问题根源 #

1. XMLTABLE自动转换日期失败：XMLTABLE中直接将<EventDateTime>和<IDateTime>映射为DATE类型时，Oracle会使用当前会话的NLS_DATE_FORMAT解析字符串，但XML中的日期格式（DD/MM/YYYY HH24:MI:SS和MM/DD/YYYY HH24:MI:SS）与默认格式不匹配，导致转换失败。
2. 冗余转换触发错误：即使XMLTABLE转换成功，后续SELECT中又用TO_DATE转换已经是DATE类型的字段，属于无效操作，且会触发格式不匹配错误。

解决方案 #

修改XMLTABLE定义，将日期字段先映射为VARCHAR2类型，再在SELECT阶段用TO_DATE指定对应格式转换，同时处理空值场景：

INSERT INTO TAB_THD_ATTND_EVENTS(
      ATND_EVNT_INDEXNO,            ATND_EVNT_USERID,             ATND_EVNT_USERNAME,           ATND_EVNT_DT,
      ATND_EVNT_ENEX_TYPE,          ATTND_EVNT_MSTR_CNTRLID,      ATTND_EVNT_DOOR_CNTRLID,      ATTND_EVNT_SPCL_FNCTNID,
      ATTND_EVNT_LEAVE_DT,          ATTND_EVNT_INSERT_DT,         ATTND_EVNT_PRCS_FLAG,         ATTND_EVNT_PRCS_DT,
      CR_DATE
 )
 SELECT 
      X.ATND_EVNT_INDEXNO,          X.ATND_EVNT_USERID,           X.ATND_EVNT_USERNAME,         TO_DATE(X.ATND_EVNT_DT,'DD/MM/YYYY HH24:MI:SS'),
      X.ATND_EVNT_ENEX_TYPE,        X.ATTND_EVNT_MSTR_CNTRLID,    X.ATTND_EVNT_DOOR_CNTRLID,    X.ATTND_EVNT_SPCL_FNCTNID,
      -- 处理LeaveDT空标签的情况
      CASE WHEN X.ATTND_EVNT_LEAVE_DT IS NOT NULL AND X.ATTND_EVNT_LEAVE_DT != '' THEN TO_DATE(X.ATTND_EVNT_LEAVE_DT,'DD/MM/YYYY HH24:MI:SS') END,
      TO_DATE(X.ATTND_EVNT_INSERT_DT,'MM/DD/YYYY HH24:MI:SS'),    'N',      '',
      SYSDATE 
 FROM TAB_TDL_ATTND_UPLOAD_TEMP T,
      (XMLTABLE('/DocumentElement/event-ta' PASSING T.ATTND_DATA_XML COLUMNS
                     ATND_EVNT_INDEXNO NUMBER PATH './IndexNo',
                     ATND_EVNT_USERID NUMBER PATH './UserID',
                     ATND_EVNT_USERNAME VARCHAR2(100) PATH './UserName',
                     -- 先映射为字符串，避免自动转换错误
                     ATND_EVNT_DT VARCHAR2(20) PATH './EventDateTime',
                     ATND_EVNT_ENEX_TYPE NUMBER PATH './EntryExitType',
                     ATTND_EVNT_MSTR_CNTRLID NUMBER PATH './MasterControllerID',
                     ATTND_EVNT_DOOR_CNTRLID NUMBER PATH './DoorControllerID',
                     ATTND_EVNT_SPCL_FNCTNID NUMBER PATH './SpecialFunctionID',
                     ATTND_EVNT_LEAVE_DT VARCHAR2(20) PATH './LeaveDT',
                     ATTND_EVNT_INSERT_DT VARCHAR2(20) PATH './IDateTime')) X
 WHERE T.ATTND_UPLOAD_NO = P_SEQNO;

补充说明 #

• 若<LeaveDT>的日期格式与<EventDateTime>不同，需调整CASE语句中的格式掩码。
• 确认XML根节点是否包含<DocumentElement>：如果实际XML没有该外层节点，需将XMLTABLE的路径改为/event-ta，否则会返回空数据集。

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