Oracle Insert Multiple Value Shows Error Message

Question

INSERT ALL
into instructor values (
835, 'SPARKS', 4000 , 200, 978, '16-12-1984')

into instructor values (
978, 'STEEL', 5000 , 250, 222, '16-01-1980')

and it was:

INSERT ALL
into instructor values (
835, 'SPARKS', 4000 , 200, 978, '16-DEC-1984')

into instructor values (
978, 'STEEL', 5000 , 250, 222, '16-JAN-1980')

The table EXISTS and SETUP CORRECTLY. Error message shows ORA-01843: not a valid month. Where did I do it wrong?

Thank you

Answer 1

You're giving a string for a DATE type column.

In cases like this, Oracle implicitly converts the string to a date according to NLS_DATE_FORMAT - see this sqlfiddle example

The right way to do that is either as Valex suggested or like this:

INSERT ALL
into instructor values (
835, 'SPARKS', 4000 , 200, 978,date '1984-12-16')
into instructor values (
978, 'STEEL', 5000 , 250, 222, date '1980-01-16')
SELECT * from DUAL;