8 Replies Latest reply: Feb 6, 2010 1:23 PM by Toon Koppelaars RSS

    SQL Error: ORA-01843

    748736
      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
          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
            Toon Koppelaars
            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
              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
                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
                  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
                    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
                      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
                        Toon Koppelaars
                        Well, apparently you should have used the abbreviation 'NIS' instead of 'APR' for April...