0

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.

Timmy Turner
  • 11
  • 1
  • 1
  • 4

3 Answers3

4

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

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.

MT0
  • 143,790
  • 11
  • 59
  • 117
2

To insert a date/time value into the Oracle table, you'll need to use the TO_DATE function. The TO_DATE function allows you to define the format of the date/time value.

For example, we could insert the '3-may-03 21:02:44' value as follows:

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.

Sergei Podlipaev
  • 1,331
  • 1
  • 14
  • 34
0

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

  • And my issue was not DATE related but with another bind cmd. So this was a red herring for me. – MTMDev May 28 '21 at 18:18