ORA-01858 FOR BEGINNER

Question

CREATE TABLE Pizza 
(
    pizza_id DECIMAL(12) NOT NULL PRIMARY KEY,
    name VARCHAR(32) NOT NULL,
    date_available DATE NOT NULL,
    price DECIMAL(4,2) NOT NULL
);


CREATE TABLE Topping 
(
    topping_id DECIMAL(12) NOT NULL,
    topping_name VARCHAR(64) NOT NULL,
    pizza_id DECIMAL(12)
);

ALTER TABLE Topping
    ADD CONSTRAINT topping_pk PRIMARY KEY(topping_id);

ALTER TABLE Topping
    ADD CONSTRAINT Topping_pizza_fk
        FOREIGN KEY(pizza_id) REFERENCES Pizza(pizza_id);

INSERT INTO pizza (pizza_id, name, date_available, price) 
VALUES (1, 'Plain', CAST('27-Feb-2021' AS DATE), 6);

Error:

ORA-01858: a non-numeric character was found where a numeric was expected

I cannot figure out which part is wrong, I'm just a beginner for SQL, it seems related with date, can someone help me?

There's typo in date_avaliable. Apart from that it runs well. See your code running at: dbfiddle.uk/… — The Impaler
I didn't notice the Oracle error code. MySQL has more difficulty with this than just the typo. — Tangentially Perpendicular
@TheImpaler However, my SQL does keep saying there's error with the date_avaliable, do you have any clue about this? — XX_User
I corrected the "available", but this time it says Error report - ORA-01858: a non-numeric character was found where a numeric was expected @KenWhite — XX_User
'27-Feb-2021' doesn't appear to be any sort of date format that can be CAST to a date. Use proper dates like '2021-02-27' instead. — Ken White

eaolson eaolson · Accepted Answer · 2021-03-04T03:40:43

This works for me: SQL Fiddle. Don't use CAST to convert strings to dates. That's the only thing that looks off about your example. It may be using a different default date format than your string. Instead use TO_DATE( '27-Feb-2021', 'DD-Mon-YYYY') which converts a string to a date, or DATE '2021-02-27', which is a date literal and only takes the yyyy-mm-dd format.

Additionally, I'd suggest using NUMBER instead of DECIMAL just because it's more standard in the Oracle world. And always use VARCHAR2 instead of VARCHAR, which is officially discouraged.

ORA-01858 FOR BEGINNER

1 Answers