oracle 在需要数字的地方发现了非数字字符
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/39790765/
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
A non-numeric character was found where a numeric was expected
提问by Timmy Turner
when trying to add new rows I get "A non-numeric character was found where a numeric was expected", should I change one of data types? Data types in "tables"
尝试添加新行时，我收到“在需要数字的地方发现非数字字符”，我应该更改其中一种数据类型吗？“表”中的数据类型
CREATE TABLE TITLES
(
TITLE_ID VARCHAR2(6) NOT NULL,
TITLE VARCHAR2(80) NOT NULL,
CATEGORY CHAR(12) NOT NULL,
PUB_ID NUMBER(4) NULL,
PRICE INTEGER NULL,
ADVANCE INTEGER NULL,
TOTAL_SALES INTEGER NULL,
NOTES VARCHAR2(200) NULL,
PUBDATE DATE NOT NULL,
CONTRACT INTEGER NOT NULL,
PRIMARY KEY (TITLE_ID),
FOREIGN KEY (PUB_ID) REFERENCES PUBLISHERS(PUB_ID)
);
Query:
询问：
INSERT INTO TITLES (TITLE_ID,TITLE,CATEGORY,PUB_ID,PRICE,ADVANCE,TOTAL_SALES,NOTES,PUBDATE,CONTRACT) VALUES ('PC8888','Secrets of Silicon Valley','popular_comp',1389,20,8000,4095,'Muckraking reporting by two courageous women on the worlds largest computer hardware and software manufacturers.','12-JUN-87',1);
Error report - SQL Error: ORA-01858: a non-numeric character was found where a numeric was expected 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.
错误报告 - SQL 错误：ORA-01858：在需要数字的地方找到了非数字字符 01858. 00000 - “在需要数字的地方找到了非数字字符” *原因：要转换的输入数据使用日期格式模型不正确。输入数据不包含数字，格式模型需要数字。*操作：修复输入数据或日期格式模型以确保元素在数量和类型上匹配。然后重试该操作。
回答by MT0
'12-JUN-87'is not a date it is a string literal.
'12-JUN-87'不是日期，而是字符串文字。
If you want to generate a date to insert into a table then you need to either:
如果要生成要插入表中的日期，则需要：
- Use an ANSI date literal:
DATE '1987-06-12' - Or, explicitly, convert a string literal to a date:
TO_DATE( '12-JUN-87', 'DD-MON-YY', 'NLS_DATE_LANGUAGE = American' )
- 使用ANSI 日期文字：
DATE '1987-06-12' - 或者，明确地将字符串文字转换为日期：
TO_DATE( '12-JUN-87', 'DD-MON-YY', 'NLS_DATE_LANGUAGE = American' )
If you try to use a string literal as a date then Oracle will try to implicitly convert it to a dateusing the NLS_DATE_FORMATsession parameter as the format mask. If this format mask does not match the format of the string then an exception will be generated.
如果您尝试使用字符串文字作为日期，那么Oracle 将尝试使用NLS_DATE_FORMAT会话参数作为格式掩码将其隐式转换为日期。如果此格式掩码与字符串的格式不匹配，则会生成异常。
Note: that the session parameters can be changed by the user and can be different for each user so you should not rely on this default value.
注意：会话参数可以由用户更改，并且每个用户都可以不同，因此您不应依赖此默认值。
回答by Sergei Podlipaev
To insert a date/time value into the Oracle table, you'll need to use the TO_DATEfunction. The TO_DATEfunction allows you to define the format of the date/time value.
要将日期/时间值插入到 Oracle 表中，您需要使用该TO_DATE函数。该TO_DATE函数允许您定义日期/时间值的格式。
For example, we could insert the '3-may-03 21:02:44'value as follows:
例如，我们可以'3-may-03 21:02:44'按如下方式插入值：
insert into table_name
(date_field)
values
(TO_DATE('2003/05/03 21:02:44', 'yyyy/mm/dd hh24:mi:ss'));
In your case apply it to '12-JUN-87'using right date pattern. You can find more infrormation about date formats here.
在您的情况下，将其应用于'12-JUN-87'使用正确的日期模式。您可以在此处找到有关日期格式的更多信息。
回答by Dunco
I was using Fedora 25, and oracle. I got the above error. I realized my data had no issue. I changed my system date settings from DENMARK to UNITED STATES and things are working fine. Maybe this can help a brother in need :-D
我使用的是 Fedora 25 和 oracle。我得到了上述错误。我意识到我的数据没有问题。我将系统日期设置从丹麦更改为美国，一切正常。也许这可以帮助有需要的兄弟:-D
