oracle 由于错误的非数字字符插入失败
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4292452/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Insert Fail because of wrong non-numeric character
提问by user422039
hy
嘿嘿
table:
桌子：
create table Players (PlayerNo number (4) not null, Name varchar2(15), date_of_birth date,leagno varchar(4));
wrong insert:
错误插入：
insert into PLAYERS (PlayerNo,Name,date_of_birth,leagno) VALUES (1,'Philipp K','Jan-10-1999','1')
whats wrong?
怎么了？
error code:
错误代码：
Fehler beim Start in Zeile 1 in Befehl:
insert into PLAYERS (PlayerNo,Name,date_of_birth,leagno) VALUES (1,'Philipp K','Jan-10-1999','1')
Fehlerbericht:
SQL-Fehler: ORA-01858: Ein nicht-numerisches Zeichen wurde gefunden, w?hrend ein numerisches Zeichen erwartet wurde
01858. 00000 - "a non-numeric character was found where a numeric was expected"
*Cause: The input data to be converted using a date format model was
incorrect. The input data did not contain a number where a number was
required by the format model.
*Action: Fix the input data or the date format model to make sure the
elements match in number and type. Then retry the operation.
回答by Donnie
*Cause: The input data to be converted using a date format model was incorrect. The input data did not contain a number where a number was required by the format model.
*原因：使用日期格式模型转换的输入数据不正确。输入数据不包含数字，格式模型需要数字。
The date string you're using doesn't match what oracle is expecting. The default format, iirc, is DD-Mon-YYYY, not Mon-DD-YYYY as you're trying to use.
您使用的日期字符串与 oracle 期望的不匹配。默认格式 iirc 是 DD-Mon-YYYY，而不是您尝试使用的 Mon-DD-YYYY。
回答by Johnbabu Koppolu
INSERT
INTO PLAYERS
(
PlayerNo,
Name,
date_of_birth,
leagno
)
VALUES
(
1,
'Philipp K',
TO_DATE('Jan-10-1999','Mon-dd-yyyy'),
'1'
)
You need to supply date using TO_DATE with the right format.
您需要使用 TO_DATE 以正确的格式提供日期。
回答by APC
The error explains:
错误说明：
"The input data to be converted using a date format model was incorrect. The input data did not contain a number where a number was required by the format model"
“使用日期格式模型转换的输入数据不正确。输入数据不包含数字，格式模型需要数字”
What this means is the value you are passing to the date_of_birth column is in the wrong format. By default Oracle expects dates to be in the format DD-MON-YYYY. You are passing a date in the format MON-DD-YYYY.
这意味着您传递给 date_of_birth 列的值格式错误。默认情况下，Oracle 期望日期采用 DD-MON-YYYY 格式。您正在以 MON-DD-YYYY 格式传递日期。
There are two - well three - ways of dealing with this.
有两种 - 三种 - 方法来处理这个问题。
- Use an explicit format mask:
to_date('Jan-10-1999', 'MON-DD-YYYY') - change the NLS_DATE_FORMAT parameter, at the session or even database level. Find out more here and here.
- change your insert statement to pass the date in the expected format.
- 使用显式格式掩码：
to_date('Jan-10-1999', 'MON-DD-YYYY') - 在会话甚至数据库级别更改NLS_DATE_FORMAT 参数。˚F IND这里了解更多，并在这里。
- 更改您的插入语句以按预期格式传递日期。
