This discussion is archived
8 Replies Latest reply: Feb 6, 2010 11:23 AM by ToonKoppelaars RSS

SQL Error: ORA-01843

748736 Newbie
Currently Being Moderated
I just write this code (by the way i use Oracle 11g release 1 + oracle sql developer)

INSERT INTO course VALUES (144,'Database Design',1195,420,'DSCHERER',TO_DATE('29-MAR-2007 20:14:33','DD-MON-YYYY HH24:MI:SS'),'ARISCHER',TO_DATE('05-APR-2007 20:14:33','DD-MON-YYYY HH24:MI:SS'));


and i got this error:

Error starting at line 1 in command:
INSERT INTO course VALUES (144,'Database Design',1195,420,'DSCHERER',TO_DATE('29-MAR-2007 20:14:33','DD-MON-YYYY HH24:MI:SS'),'ARISCHER',TO_DATE('05-APR-2007 20:14:33','DD-MON-YYYY HH24:MI:SS'))
Error report:
SQL Error: ORA-01843: geçerli bir ay de─čil
01843. 00000 - "not a valid month"
*Cause:   
*Action:


WHAT SHOULD I DO? i dont understand why is not a valid month?????
  • 1. Re: SQL Error: ORA-01843
    Centinul Guru
    Currently Being Moderated
    Based on the language of the error, it looks like you are trying to insert dates defined in english when your client side NLS settings are probably set to another language.

    What are the results of the following query?
    SELECT * FROM NLS_SESSION_PARAMETERS;
  • 2. Re: SQL Error: ORA-01843
    ToonKoppelaars Employee ACE
    Currently Being Moderated
    Seems like you are running this session with your language (i.e. not English).
    If you do this:
    select to_char(sysdate+30, 'DD-MON-YYYY') some_march_date
          ,to_char(sysdate+60,'DD-MON-YYYY') some_april_date
    from dual
    /
    How is the month March displayed then?
    And April?
  • 3. Re: SQL Error: ORA-01843
    748736 Newbie
    Currently Being Moderated
    PARAMETER VALUE
    ------------------------------ ----------------------------------------
    NLS_LANGUAGE TURKISH
    NLS_TERRITORY TURKEY
    NLS_CURRENCY TL
    NLS_ISO_CURRENCY TURKEY
    NLS_NUMERIC_CHARACTERS ,.
    NLS_CALENDAR GREGORIAN
    NLS_DATE_FORMAT DD-MON-RR
    NLS_DATE_LANGUAGE TURKISH
    NLS_SORT TURKISH
    NLS_TIME_FORMAT HH24:MI:SSXFF
    NLS_TIMESTAMP_FORMAT DD/MM/RRRR HH24:MI:SSXFF
    NLS_TIME_TZ_FORMAT HH24:MI:SSXFF TZR
    NLS_TIMESTAMP_TZ_FORMAT DD/MM/RRRR HH24:MI:SSXFF TZR
    NLS_DUAL_CURRENCY YTL
    NLS_COMP BINARY
    NLS_LENGTH_SEMANTICS BYTE
    NLS_NCHAR_CONV_EXCP FALSE



    It is Turkish, so should i change the lang??? if so pls tell me how??

    thanks.
  • 4. Re: SQL Error: ORA-01843
    Frank Kulash Guru
    Currently Being Moderated
    Hi,

    You can use whatever language you want in TO_DATE, regardless of the NLS settings, by using the 3rd parameter:
    TO_DATE ( '29-MAR-2007 20:14:33'
         , 'DD-MON-YYYY HH24:MI:SS'
         , 'NLS_DATE_LANGUAGE = English'
         )
    Use the 3rd argument to TO_DATE (and TO_CHAR) if you can't be certain of the NLS settings.
  • 5. Re: SQL Error: ORA-01843
    748736 Newbie
    Currently Being Moderated
    PARAMETER VALUE
    ------------------------------ ----------------------------------------
    NLS_LANGUAGE TURKISH
    NLS_TERRITORY TURKEY
    NLS_CURRENCY TL
    NLS_ISO_CURRENCY TURKEY
    NLS_NUMERIC_CHARACTERS ,.
    NLS_CALENDAR GREGORIAN
    NLS_DATE_FORMAT DD-MON-RR
    NLS_DATE_LANGUAGE TURKISH
    NLS_SORT TURKISH
    NLS_TIME_FORMAT HH24:MI:SSXFF
    NLS_TIMESTAMP_FORMAT DD/MM/RRRR HH24:MI:SSXFF
    NLS_TIME_TZ_FORMAT HH24:MI:SSXFF TZR
    NLS_TIMESTAMP_TZ_FORMAT DD/MM/RRRR HH24:MI:SSXFF TZR
    NLS_DUAL_CURRENCY YTL
    NLS_COMP BINARY
    NLS_LENGTH_SEMANTICS BYTE
    NLS_NCHAR_CONV_EXCP FALSE

    17 rows selected
    SOME_MARCH_DATE SOME_APRIL_DATE
    --------------- ---------------
    08-MAR-2010 07-NIS-2010




    yes Sir ??????
  • 6. Re: SQL Error: ORA-01843
    Centinul Guru
    Currently Being Moderated
    If you want to change the NLS_DATE_LANGUAGE you could do the following:
    ALTER SESSION SET NLS_DATE_LANGUAGE='ENGLISH';
    Or under the Database Preferences in SQL Developer change your NLS parameters there.
  • 7. Re: SQL Error: ORA-01843
    748736 Newbie
    Currently Being Moderated
    Now i got this Error:

    Error starting at line 1 in command:
    INSERT INTO course VALUES (146,'Java for C/C++ Programmers',1195,NULL,'DSCHERER',TO_DATE('29-MAR-2007 20:14:33','DD-MON-YYYY HH24:MI:SS'),'ARISCHER',TO_DATE('05-APR-2007 20:14:33','DD-MON-YYYY HH24:MI:SS'))
    Error report:
    SQL Error: ORA-00001: unique constraint (STUDENT.CRSE_PK) violated
    00001. 00000 - "unique constraint (%s.%s) violated"
    *Cause:    An UPDATE or INSERT statement attempted to insert a duplicate key.
    For Trusted Oracle configured in DBMS MAC mode, you may see
    this message if a duplicate entry exists at a different level.
    *Action:   Either remove the unique restriction or do not insert the key.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
  • 8. Re: SQL Error: ORA-01843
    ToonKoppelaars Employee ACE
    Currently Being Moderated
    Well, apparently you should have used the abbreviation 'NIS' instead of 'APR' for April...

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points