Timmy Turner Timmy Turner - 2 months ago 13
SQL Question

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)
);


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.

MT0 MT0
Answer

'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:

  • 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' )

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.

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.