ORA-01843: not a valid month

From: Shailesh <shailesh.saraff_at_gmail.com>
Date: 15 Dec 2005 10:03:18 -0800
Message-ID: <1134669798.078730.111140@g47g2000cwa.googlegroups.com>

Hello,

Last few days we are observing ORA-01843: not a valid month, error continously.
On startup our Application always sets date format to dd.mm.yyyy format using
ALTER SESSION SET NLS_DATE_FORMAT = 'DD.MM.YYYY'; But error occurs on following statement. SELECT Type, Module FROM TableTypeModule WHERE NVL(TRUNC(BeginDate), '01.01.1800') <= TRUNC(SYSDATE) AND TRUNC(EndDate) >= TRUNC(SYSDATE);

We have also done following to check, what was the date format when this error
occurs and to check in case someone else has set other date format, but even
following trace/debug trigger shows DD.MM.YYYY format. We can't change all
queries in our application to set date format explicitly.

CREATE OR REPLACE TRIGGER TrgDB_003

   AFTER SERVERERROR ON DATABASE
DECLARE

   sprogram     VARCHAR2 (200);
   smachine     VARCHAR2 (200);
   sosuser      VARCHAR2 (200);
   NLSHost      VARCHAR2 (4000);
   NLSDate      VARCHAR2 (4000);
   NLSLang      VARCHAR2 (4000);
   SPID         VARCHAR2 (10);
   sid             number;
   serial         number;

BEGIN    IF (is_servererror (1843))
   THEN

      SELECT NVL (b.Program, b.Module), machine, OSUser, a.SPID,
             SYS_CONTEXT ('USERENV', 'HOST'),
             SYS_CONTEXT ('USERENV', 'NLS_DATE_FORMAT'),
             SYS_CONTEXT ('USERENV', 'NLS_DATE_LANGUAGE'), b.SID,
b.Serial#
        INTO sProgram, smachine, sosuser, SPID, NLSHost,
             NLSDate,
             NLSLang, SId, serial
        FROM SYS.v_$session b, v$process a
       WHERE audsid = USERENV ('SESSIONID') AND A.Addr = B.Paddr;

      INSERT INTO TempTraceInformation
                  (EventTime, Program, machine, osuser,
                   Information, TraceFileSPID,
                   NLSHost, NLSDate, NLSLang, sid, serial
                  )
           VALUES (SYSDATE, sprogram, smachine, sosuser,
                   'Caught ORA-1843 exception with this program', SPID,
                   NLSHost, NLSDate, NLSLang, sid, serial
                  );

    END IF;
END;
/

We are really in need of help to resolve this issue, please help. How can we
track that date format?

Thanks & Regards,

Shailesh Received on Thu Dec 15 2005 - 12:03:18 CST