1
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

Ram Dutt Shukla
  • 1,351
  • 7
  • 28
  • 55
weia design
  • 1,250
  • 2
  • 14
  • 22
  • check this [answer](http://stackoverflow.com/questions/13107900/ora-01843-not-a-valid-month). Might be helpful. Also [see this](http://www.techonthenet.com/oracle/errors/ora01843.php) – Mohammad Faisal Mar 18 '13 at 12:09

4 Answers4

2

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;
Community
  • 1
  • 1
A.B.Cade
  • 16,735
  • 1
  • 37
  • 53
1

use month as a 3 letter character not a number instead of 12 use dec

Pradeep
  • 563
  • 1
  • 3
  • 16
1

Just use TO_DATE to convert string into date 

INSERT ALL
into instructor values (
835, 'SPARKS', 4000 , 200, 978,TO_DATE('16-12-1984','DD-MM-YYYY'))
into instructor values (
978, 'STEEL', 5000 , 250, 222,TO_DATE('16-01-1980','DD-MM-YYYY'))
SELECT * from DUAL;
valex
  • 23,966
  • 7
  • 43
  • 60
0

Yor are trying to insert DATE without mentioning the format, so oracle is using its default DATE fromat which DD-MON-YYYY

You can try on the SQL Fiddle

Ram Dutt Shukla
  • 1,351
  • 7
  • 28
  • 55
  • That's not accurate, Oracle Date doesn't have a format, nor a difault one. The problem is that he's giving a string instead of a date so oracle must convert it. For this it uses the NLS_DATE_FORMAT parameter (which AFAIK does have a default value as you mentioned) – A.B.Cade Mar 18 '13 at 13:36