发现非数字字符，该数字应为数字 [英] A non-numeric character was found where a numeric was expected
问题描述
当尝试添加新行时，出现在非预期的数字位置发现了非数字字符"，我应该更改其中一种数据类型吗? 表格"中的数据类型
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)
);
查询:
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);
错误报告- SQL错误:ORA-01858:在期望数字的位置找到了非数字字符 01858.00000-在期望数字的位置发现了非数字字符" *原因:使用日期格式模型转换的输入数据为 不正确.输入数据不包含数字，其中数字为 格式模型要求. *操作:修正输入数据或日期格式模型，以确保 元素在数量和类型上匹配.然后重试该操作.
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.
推荐答案
'12-JUN-87'不是日期，而是字符串文字.
'12-JUN-87' is not a date it is a string literal.
如果要生成要插入表中的日期，则需要执行以下操作之一:
If you want to generate a date to insert into a table then you need to either:
- 使用 ANSI日期文字:
DATE '1987-06-12' - 或者明确地将字符串文字转换为日期:
TO_DATE( '12-JUN-87', 'DD-MON-YY', 'NLS_DATE_LANGUAGE = American' )
- 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' )
如果您尝试使用字符串文字作为日期，则 Oracle会尝试使用以下方式隐式将其转换为日期 NLS_DATE_FORMAT会话参数作为格式掩码.如果此格式掩码与字符串的格式不匹配，则会生成异常.
If you try to use a string literal as a date then Oracle will try to implicitly convert it to a date using the NLS_DATE_FORMAT session parameter as the format mask. If this format mask does not match the format of the string then an exception will be generated.
注意:会话参数可以由用户更改，并且每个用户可以不同，因此您不应依赖此默认值.
这篇关于发现非数字字符，该数字应为数字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
