英文:
ORA-01830: date format picture ends before converting entire input string error
问题
以下是您的插入查询的中文翻译部分：
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;
请注意，我只翻译了您的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;
Above written is my Insert Query in PLSQL, and given below is the XML String Data that I'm trying to insert, but I'm getting the error mentioned. Can anyone help me out with this?
<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
得分: 0
为了回答你的问题，问题出现在你将ATTND_EVNT_INSERT_DT定义为DATE的地方，这是不正确的，因为元素日期格式不正确（与默认的Oracle日期格式不同）。
为了解决你的问题，我建议使用以下简化的命令（不包括INSERT，因为我认为问题不在于INSERT SQL语句）。
SELECT TO_DATE(X.ATND_EVNT_DT ,'DD/MM/YYYY HH24:MI:SS')
,TO_DATE(X.ATTND_EVNT_INSERT_DT,'MM/DD/YYYY HH24:MI:SS')
FROM TAB_TDL_ATTND_UPLOAD_TEMP T
,(XMLTABLE('/DocumentElement/event-ta'
PASSING T.ATTND_DATA_XML
COLUMNS ATND_EVNT_DT VARCHAR2(20) PATH './EventDateTime'
,ATTND_EVNT_INSERT_DT VARCHAR2(20) PATH './IDateTime'
)
) X
WHERE T.ATTND_UPLOAD_NO = P_SEQNO;
我只是将XML部分中的DATE替换为VARCHAR2(20)。
如果这个命令不起作用，请告诉我们以下命令返回什么：
SELECT X.ATND_EVNT_DT
,X.ATTND_EVNT_INSERT_DT
FROM TAB_TDL_ATTND_UPLOAD_TEMP T
,(XMLTABLE('/DocumentElement/event-ta'
PASSING T.ATTND_DATA_XML
COLUMNS ATND_EVNT_DT VARCHAR2(20) PATH './EventDateTime'
,ATTND_EVNT_INSERT_DT VARCHAR2(20) PATH './IDateTime'
)
) X
WHERE T.ATTND_UPLOAD_NO = P_SEQNO;
希望这可以帮助你解决问题。
英文:
To answer to your question that is "Why this", the problem occurs because your definition of ATTND_EVNT_INSERT_DT as DATE is incorrect because element date format is incorrect (certainly distinct from default Oracle Data Format).
To solve your problem, I propose to use following reducted command (without INSERT because I don't think that problem is in INSERT Sql statement).
SELECT TO_DATE(X.ATND_EVNT_DT ,'DD/MM/YYYY HH24:MI:SS')
,TO_DATE(X.ATTND_EVNT_INSERT_DT,'MM/DD/YYYY HH24:MI:SS')
FROM TAB_TDL_ATTND_UPLOAD_TEMP T
,(XMLTABLE('/DocumentElement/event-ta'
PASSING T.ATTND_DATA_XML
COLUMNS ATND_EVNT_DT VARCHAR2(20) PATH './EventDateTime'
,ATTND_EVNT_INSERT_DT VARCHAR2(20) PATH './IDateTime'
)
) X
WHERE T.ATTND_UPLOAD_NO = P_SEQNO;
I have only replace DATE in XML part by VARCHAR2(20).
If this command doesn't work, inform us about what is returning following command
SELECT X.ATND_EVNT_DT
,X.ATTND_EVNT_INSERT_DT
FROM TAB_TDL_ATTND_UPLOAD_TEMP T
,(XMLTABLE('/DocumentElement/event-ta'
PASSING T.ATTND_DATA_XML
COLUMNS ATND_EVNT_DT VARCHAR2(20) PATH './EventDateTime'
,ATTND_EVNT_INSERT_DT VARCHAR2(20) PATH './IDateTime'
)
) X
WHERE T.ATTND_UPLOAD_NO = P_SEQNO;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论